Problem with monthly average measure

[Dunner2020]

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.

[amitchandak]

@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]))

[Dunner2020]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak · Accepted solution]

@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]))

[sevenhills]

will this work?

Monthly avg_measure 2 = IF(  SUM('5years'[Value]) , 
 CALCULATE(   AVERAGEX( VALUES(Dates[RY Month Number]),  SUM('5years'[Value]) ), ALLEXCEPT(Dates, Dates[RY Month Number])), blank())

[Megha166]

Can you share the excel spreadsheet with Data OR the list of coluns in base Table. I can write the measure on that Data.

[Dunner2020]

Hi @Megha166 ,

Here is the link of pibx file https://1drv.ms/u/s!AucycxZHFe9TjS6G96JVtgslQLBX?e=1Njrdx