Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I have got six years of data. Its regulatory year starts on 1st April and finishes on 31st March. I want to calculate the cumulative average of the sum on a monthly basis for the last 5 years (don't want to include the current year). I am doing the following steps to calculate:
Calculate the sum of value using following measure:
As you can see that cumulative Norm Value does not cumulative add Average Normalised Value. I am not sure where I made the mistake. Could anyone help me in fixing the issue?
Solved! Go to Solution.
Hi @Dunner2020,
According to your description, It sounds like you want to apply multiple aggregates on measure calculation, right?
IF that is the case, I'd like to suggest you add a variable with summarize function to apply the first aggregation, then you can use iterator functions to invoke the above variable table to calculate the second aggregate.
Measure Totals, The Final Word
measure =
VAR summary =
SUMMARIZE ( Table, [category 1], [category 2], "AVG", AVERAGE ( Table[Amount] ) )
RETURN
SUMX ( summary, [AVG] )
Regards,
Xiaoxin Sheng
Hi @Dunner2020,
According to your description, It sounds like you want to apply multiple aggregates on measure calculation, right?
IF that is the case, I'd like to suggest you add a variable with summarize function to apply the first aggregation, then you can use iterator functions to invoke the above variable table to calculate the second aggregate.
Measure Totals, The Final Word
measure =
VAR summary =
SUMMARIZE ( Table, [category 1], [category 2], "AVG", AVERAGE ( Table[Amount] ) )
RETURN
SUMX ( summary, [AVG] )
Regards,
Xiaoxin Sheng
"I want to calculate the cumulative average"
That's not a thing. Did you mean to say: "I want to calculate the average of the monthly sums" ?
Your AVERAGEX measure is missing the monthly bucketing.
Which average are you trying to compute? Across all months regardless of year?
What would be the expected result?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
94 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
128 | |
102 | |
98 |