Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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
shop_id | date | customers |
AAA | 2020/11/1 | 5000 |
AAA | 2020/11/2 | 3000 |
AAA | 2020/12/1 | 4000 |
AAA | 2020/12/2 | 1000 |
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.
@Anonymous , Try like
averageX(values(Date[Month Year]),[monthly % change])
@Anonymous , how this giving 3, is not clear
you can have like calculate(DISTINCTCOUNT (calendar[year-month]), allselected(calendar))
@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.
@Anonymous , Try like
averageX(values(Date[Month Year]),[monthly % change])
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.
https://1drv.ms/u/s!AsNOXcTG8UttgdoAYPJr9xfg1HBBsg?e=3fQNpZ
If you have any other idea, just tell me. I am happy to try!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |