Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tborg
Helper I
Helper I

Calculating variance from an average

Hi all!  I have the following table visualization:

Capture.PNG

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 8
bhanson5587
New Member

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.

dani0010
Frequent Visitor

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%.

Vvelarde
Community Champion
Community Champion

@tborg

 

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




Lima - Peru

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.

Zubair_Muhammad
Community Champion
Community Champion

Hi @tborg

 

Give this MEASURE a shot

 

Variance =
VAR Total =
    SUMX ( ALLSELECTED ( TableName[Category] ), [Ave,Earnings] )
RETURN
    DIVIDE ( [Ave.Earnings], Total ) - 1

Regards
Zubair

Please try my custom visuals

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.