cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tborg Regular Visitor
Regular Visitor

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
Super User
Super User

Re: Calculating variance from an average

Hi @tborg

 

Give this MEASURE a shot

 

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

Re: Calculating variance from an average

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
tborg Regular Visitor
Regular Visitor

Re: Calculating variance from an average

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.

tborg Regular Visitor
Regular Visitor

Re: Calculating variance from an average

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

Super User
Super User

Re: Calculating variance from an average

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bhanson5587 New Member
New Member

Re: Calculating variance from an average

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.

tborg Regular Visitor
Regular Visitor

Re: Calculating variance from an average

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.

Highlighted
tborg Regular Visitor
Regular Visitor

Re: Calculating variance from an average

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.