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!