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 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.
Solved! Go to 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]))
@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.
Hi,
You may download my PBI file from here.
Hope this helps.
@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]))
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())
Can you share the excel spreadsheet with Data OR the list of coluns in base Table. I can write the measure on that Data.
Hi @Megha166 ,
Here is the link of pibx file https://1drv.ms/u/s!AucycxZHFe9TjS6G96JVtgslQLBX?e=1Njrdx
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |