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
dani0010's avatar
dani0010
Advocate II
8 years ago

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

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