Forum Discussion

tborg's avatar
tborg
Helper I
8 years ago

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 amoun...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

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's avatar
tborg
Helper I
8 years ago

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.