Cumulative sum on average value and month as axis

[krishb1414]

Hi Everyone,

I have average measure and I want to show bar chart with average by year and drill down to month. 

while showing in month I want values like cumulative. 

For example, Jan = Jan, Feb= Jan + Feb like this.

Measure: calculate ([average measure], filter(all(dim_date), dim_date <= max(dim_date).

 as per above measure I m getting sum by month but not cumulative value. 

• Any one help me on this , Thanks 

[Anonymous · Accepted solution]

Hi @krishb1414 ,

Based on my testing again, please try the following methods again:

1.Create the sample table.

2.Create the new measure to calculate the month average.

Month average = AVERAGEX('Table date', 'Table date'[Values])

3.Create the new measure to cumulative sum average.

Cumulative sum month = 
var sel_year = SELECTEDVALUE('Table date'[Year])
var sel_month = SELECTEDVALUE('Table date'[Month])
VAR _vtable = SUMMARIZE(ALLSELECTED('Table date'), 'Table date'[Year], 'Table date'[Month], "_Sum", [Month average])
VAR _vtable2 = ADDCOLUMNS(_vtable, "_Less", SUMX(FILTER(_vtable,[Month] <= sel_month && [Year] = sel_year), [_Sum]))
RETURN 
SUMX(FILTER(_vtable2, [Month] = MAX('Table date'[Month]) && [Year] = MAX('Table date'[Year])), [_Less])

4.Drag the measures into the bar chart. Set the Date column to show year and month.

5.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.