Forum Discussion

csrapp96's avatar
csrapp96
Helper I
2 years ago
Solved

Average of Multiple Growth Rates

Hello PBI Community,

I found a way to average the values across my table and come up with a total average growth rate. But I need the average of the growth rates for each line item. I can't find a good way to do this. 

I have a table with multiple rows and then a 1, 3, and 5 year growth rate beside it. Here is the 1 year formula for one of the field parameters I am using:

Sales Total % =
VAR MAXDATE = MAX('Summary'[Year])
VAR PARAMETERVALUE = 'Years'[Years Value]

RETURN
 DIVIDE( CALCULATE(SUM(Summary[Sales]), 'Summary'[Year] = MAXDATE) ,
 CALCULATE(SUM(Summary[Sales]), 'Summary'[Year] = MAXDATE - 1))^(1/1)-1


I want a line below in its own table that averages the growh rates in my table. So if the output in the table is 2% and 1%, I want an average below that shows 1.5%. What it's doing is adding all the values in current year and prior year and then finding the average - which I don't want it to do. 

AVERAGE % =
VAR THISYEAR = CALCULATE(SUM(Summary[Sales]),ALLSELECTED(Summary),Summary[Year]=MAX(Summary[Year]))
VAR LASTYEAR = CALCULATE(SUM(Summary[Sales]),ALLSELECTED(Summary),Summary[Year]=MAX(Summary[Year])-1)
VAR DIFF = ((THISYEAR-LASTYEAR)/LASTYEAR)
RETURN

AVERAGEX(VALUES(Summary[Year]), DIFF)


Any simple way I am missing to take the average of the growth rates and not the average of all the table values summed together and then averaged?  I was trying allexcept but I still can't get it to work right.

Thank you!!

Let me provide more detail. Here is what I am trying to do:

Team A202525%
Team B1530100%
average  63%


Here is what we are getting:

TEAM A&B 355557%

 

How do I get it to calculate the 63% average of the growth rates rather than the average of the totals 57%?

Need Help
  • csrapp96's avatar
    csrapp96
    2 years ago

    I solved it! 

    I needed to use the AVERAGEX(Summarize(Summary,Summary[Team], [Growth Measure])

    This in effect groups the team measures and then finds the average of them 🙂

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello csrapp96,

     

    Can you please try this DAX:

     

    Average Sales Growth Rate = 
AVERAGEX(
    Summary, 
    VAR CurrentYearSales = CALCULATE(SUM(Summary[Sales]), Summary[Year] = MAX(Summary[Year]))
    VAR PreviousYearSales = CALCULATE(SUM(Summary[Sales]), Summary[Year] = MAX(Summary[Year]) - 1)
    RETURN
    IF(
        PreviousYearSales <> 0,
        (CurrentYearSales - PreviousYearSales) / PreviousYearSales,
        BLANK()
    )
)

     

    • csrapp96's avatar
      csrapp96
      Helper I
      2 years ago

      Hi Sahir_Maharaj,

      Thanks for the reply. Sure thing - that output is exactly the result I am getting now. 

      Let me provide more detail. Here is what I am trying to do:

      Team A202525%
      Team B1530100%
      average  63%


      Here is what we are getting:

      TEAM A&B 355557%

       

      How do I get it to calculate the 63% average of the growth rates rather than the average of the totals 57%?

      Would appreciate any help!

    • Sahir_Maharaj's avatar
      Sahir_Maharaj
      Super User
      2 years ago

      Thanks csrapp96.

       

      Can you please try this approach:

      Average Growth Rate = 
AVERAGEX(
    Summary,
    VAR CurrentYearSales = CALCULATE(SUM(Summary[Sales]), Summary[Year] = MAX(Summary[Year]))
    VAR PreviousYearSales = CALCULATE(SUM(Summary[Sales]), Summary[Year] = MAX(Summary[Year]) - 1)
    RETURN
        (CurrentYearSales - PreviousYearSales) / PreviousYearSales
)
      • csrapp96's avatar
        csrapp96
        Helper I
        2 years ago

        Thanks! It gets me to the average for each line. 

        RETURN CurrentYearSales is correct
        RETURN PreviousYearSales is correct

        but 

        RETURN AVERAGEX(...... (CurrentYearSales - PreviousYearSales) / PreviousYearSales ) is calculating as "Infinity"

         
        As a check, RETURN (CurrentYearSales-PreviousYearSales) doesn't calculate correctly either.

        I'm playing around with it and feel like it's close but still not quite right.

        Thank you