Forum Discussion
Problem with monthly average measure
Hi there,
Sorry for asking for a trivial issue. I have been stuck on the calculation for hours. I have got five years of monthly data. I want to calculate the cumulative sum of the average monthly data. For this purpose, I first calculate the measure that calculates the sum of the value for on the monthly basis:
sum_measure = sum(table[value])
As I have five years of data which means that each month has 5 values. So the next step is to calculate the Monthly average of the entire data set. So I created the monthly average measure using the following measure:
Monthly_average_measure = Averagex(values(Date['Month']),[sum_measure])
When I displayed the value and both measures in the table then it shows the following output:
As you can see that all three column produces the same value.
and If I include the year column in the table, it shows following output:
I was expecting that same RY Month Number column should have the same Monthly average_measure value but that is not the case.
I am not sure where I made the mistake. Any help would be really appreciated.
Dunner2020 , then you need to RY Month NO
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[RY Month NO]))
better would be
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), filter(allselected(Date),Date[RY Month NO])=max(Date[RY Month NO]))
7 Replies
Hi Anonymous ,
Here is the link of pibx file https://1drv.ms/u/s!AucycxZHFe9TjS6G96JVtgslQLBX?e=1Njrdx
Dunner2020 , fi you take only yeat then if will give Monthly Average. Else use allexpect for year and try
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[Year]))
amitchandak , When I used your formula, it produces same monthly average as shown below:
I am expecting that same month should have same value. For example, the monthly average of April in 2016 should be the same as in April 2017 because its monthly average of 5 years.
Dunner2020 , then you need to RY Month NO
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[RY Month NO]))
better would be
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), filter(allselected(Date),Date[RY Month NO])=max(Date[RY Month NO]))
