Calculating variance from an average

[tborg]

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

[bhanson5587]

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]

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]

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]

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]

@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

[tborg]

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]

Hi @tborg

Give this MEASURE a shot

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

[tborg]

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.