cancel
Showing results for
Did you mean:
Helper III

## Average of monthly percentage change

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?

1 ACCEPTED SOLUTION
Super User IV

@nabe , Try like

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

5 REPLIES 5
Super User IV

@nabe , how this giving 3, is not clear

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper III

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

Super User IV

@nabe , Try like

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper III

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!
Helper III

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!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group