Power BI - Table Visualization Totals Row

Question

I have created the following table as a vizualization in Power BI.

The blue columns are measures I created. I need the totals for those columns to equal the sum of the visible values on in the table. Do I need to create the variances as calculated columns instead of measures?

The desired result would be for the Unit Variance column to total $3.87 and the Total Variance column to total $923.76.

Unit Variance is being calculated as follows

average('Sent'[SentAmount]) - average(Received[Received Amount])

Total Variance is being calculated as follows

[Unit Variance] * sum('Sent'[Sent Volume])

It's possible to force the rows to sum to the total, but averages and variances are not generally additive (if the average height for men is 70 inches and the average height for women is 64 inches, that doesn't mean the average adult is 70 + 64 = 134 inches). How would you interpret the values that you are asking for? — Alexis Olson
You would weight the averages by percentage of total volume and then add them to get a total. For example, (average adult male height * percentage of adult males) + (average adult female height * percentage of adult females) = average adult height. As far as adding all the variances, that would give you a net variance in real dollars. — GTA
If you need to force the total to be the sum of the parts, try the patterns here: powerpivotpro.com/2012/03/… — Alexis Olson

GTA GTA · Accepted Answer · 2018-03-27T14:19:23

Here is the answer I came up with

I left the unit variance as a measure

unit variance = average('Sent'[SentAmount]) - average(Received[Received Amount])

But I made the total variance a column

Total Variance = ((SUMX(Filter('Sent','Sent'[Sent Volume] = 1),'Sent'[Sent Volume])) / sum('Sent'[Sent Volume])) * [Unit Variance]

I have a column in my sent table that puts a "1" in the row to count volume.

Power BI - Table Visualization Totals Row

1 Answers