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

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

      • csrapp96's avatar
        csrapp96
        Helper I
        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 🙂