Average of Multiple Growth Rates

[csrapp96]

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 A	20	25	25%
Team B	15	30	100%
average			63%

Here is what we are getting:

TEAM A&B

35

55

57%

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

[Sahir_Maharaj]

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()
    )
)

[Sahir_Maharaj]

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]

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 · Accepted solution]

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 🙂

[csrapp96]

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 A	20	25	25%
Team B	15	30	100%
average			63%

Here is what we are getting:

TEAM A&B

35

55

57%

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!