Forum Discussion
Calculating variance from an average
Hi all! I have the following table visualization:
What I want to do is to create a column that calculates the variance of Ave. Earnings in each row compared to the variance of the total amount (170,775.83) but cannot figure it out. I want the result to go in the column, "Variance from Ave. Earnings." All the columns displayed are Measures I created.
The answers should be the following:
-14.2%
8.76%
20.55%
28.23%
50.85%
60.17%
112.68%
106.88%
216.82%
Then the Total should be 100%.
Thanks in advance!
Tom
8 Replies
Hi tborg
Give this MEASURE a shot
Variance = VAR Total = SUMX ( ALLSELECTED ( TableName[Category] ), [Ave,Earnings] ) RETURN DIVIDE ( [Ave.Earnings], Total ) - 1No, that did not work. It gave me a column for Variance that was mostly -71% to -90%. I will play with the formula and see if it's just a matter of finding the right columns/measures to use.
Try something like:
Answer =
VAR sumAveEarnings = CALCULATE(SUM([Ave. Earnings]),ALL(TABLE1[Category]))
RETURN
(AveEarnings/sumAveEarnings)-1I believe this should work but haven't actually tested it in PBI. Let me know if you have any issues.
Thanks, but this is not working. Please see post below. Ave. Earnings is a measure, and the SUM function requires a column reference, so I am still stuck.
You could do this all in one metric, but I would break it up into two as follows:
Overall Ave Earnings = calculate([ave earnings],all())
Then the one you're after:
Variance from Ave Earnings = ave earnings / overall ave earnings
This did not work. First, I needed something after the ALL command. I changed the formula to:
Overall Ave Earnings = calculate(TotalEarnings[Ave. Earnings],ALL(TotalEarnings[Earnings]))
But this just duplicated my Ave. Earnings column, so that the Variance from Ave. Earnings is 100%.
