I need the average of a monthly percentage change.
What I need to do is : SUM ([monthly % change]) / DISTINCTCOUNT (calendar[year-month]) )
The issue I am facing is that I calculated the monthly percentage change with a measure. And I cannot SUM a measure.
I have following columns
I calculated the following measures to get the monthly percentage change:
1. total = SUM(table[customers])
2. total_LM = CALCULATE([total], PREVIOUSMONTH(calendar[date]))
3.monthly % change = DIVIDE( [total], [total_LM]), BLANK())-1
|shop_id||year-month||total||monthly % change (measure)|
|AAA||2020/ 11||8000||+20 %|
|AAA||2020/ 12||5000||-37.5 %|
|AAA||2021/ 1||6000||+20 %|
|AAA||2021/ 2||5000||-16.67 %|
I want to calculate SUM (monthly % change) / DISTINCTCOUNT (year-month) to get the average, so:
-14.17 / 4 = -3.54
Can anybody help me with this?
Solved! Go to Solution.
@amitchandak sorry , thanks for pointing this out. I edited the post.
I know how to calculate the monthly distinctcount,
but I don't know how to get the average of the monthly percentage change.
I thought I need to SUM all the monthly changes and divide by month.
sorry, I tried your proposed measure again and I think it works.
thanks, I tried the measure you were proposing, but it doesn't give me the correct result.
I created a test pbx. file. Kindly see the link below.
If you have any other idea, just tell me. I am happy to try!
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.