Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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%?
Solved! Go to 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 🙂
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()
)
)
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
)
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
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 🙂
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!
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |