Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Try something like:
Answer =
VAR sumAveEarnings = CALCULATE(SUM([Ave. Earnings]),ALL(TABLE1[Category]))
RETURN
(AveEarnings/sumAveEarnings)-1
I 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%.
Don't know how is your AVE Earnings but try with:
Measure = DIVIDE(SUM(Table1[AVE Earnings]);CALCULATE(Sum(Table1[AVE Earnings]);ALLSELECTED(Table1)))-1
Replace SUM(Table1[AVE Earnings]) with your right measure or column
Let me know what is the result
Regards
Victor
Ave. Earnings is a measure, written as follows:
Ave. Earnings =
CALCULATE( TotalPlans[Accum Earnings] ) / ( TotalPlans[Number of Advisors] )
Accum Earnings is also a measure:
Accum Earnings =
IF (
MIN ( TotalPlans[Plan Index] ) = 1,
SUM ( TotalEarnings[Earnings] ),
CALCULATE (
SUM ( TotalEarnings[Earnings] ),
FILTER ( ALLSELECTED ( TotalPlans ), TotalPlans[Plan Index] >= MIN ( TotalPlans[Plan Index] ) )
)
)
So your formula will not allow me to enter just the measure, as it requires a column, but this is not a column, so it is not working.
Hi @tborg
Give this MEASURE a shot
Variance = VAR Total = SUMX ( ALLSELECTED ( TableName[Category] ), [Ave,Earnings] ) RETURN DIVIDE ( [Ave.Earnings], Total ) - 1
No, 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |