Average of monthly percentage change

[Anonymous]

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?

[amitchandak]

@Anonymous , how this giving 3, is not clear

you can have like calculate(DISTINCTCOUNT (calendar[year-month]), allselected(calendar))

[Anonymous]

@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.

[amitchandak · Accepted solution]

@Anonymous , Try like

averageX(values(Date[Month Year]),[monthly % change])

[Anonymous]

@amitchandak 

sorry, I tried your proposed measure again and I think it works.

average= AVERAGEX(VALUES('calendar'[year/month]),[monthly % change])

Thank you very much for your help!

[Anonymous]

@amitchandak 

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!