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

## 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 8 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

## 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

## 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

## 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

## 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

## 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

## 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

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