cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dunner2020
Post Prodigy
Post Prodigy

Cumulative sum of average measure

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:

Sum Normalised Value = CALCULATE(sum('Fact table'[30 Mins Normalised Value]), FILTER('Date','Date'[Date] <[Current_RY_Yr_Strt] && 'Date'[Date] >= [last 5 years]))
Average of sum using following measure:
Average Normalized Value = AVERAGEX( VALUES('Date'[RY Month Year Sort]),[Sum Normalised Value])
Cumulative sum of average using following measure:
 
Cumulative Norm Value =
Var current_date = MAX('Fact table'[Date] )
RETURN
SUMX(
FILTER(ALL('Date'),'Date'[Date] <= current_date
&& 'Date'[Date] < [Current_RY_Yr_Strt] ), [Average Normalized Value])
 
When I ran the report it produces the following output:
 
leo_89_0-1619136508194.png

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?

 
Sample file can be download from here.
 
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

"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?

 

lbendlin_0-1619397784501.png

What would be the expected result?

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors