Cumulative sum on average value and month as axis

krishb1414 — Thu, 27 Jun 2024 17:55:44 GMT

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

Re: Cumulative sum on average value and month as axis

Anonymous — Fri, 28 Jun 2024 03:26:18 GMT

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.

topic Re: Cumulative sum on average value and month as axis in DAX Commands and Tips

Cumulative sum on average value and month as axis

Re: Cumulative sum on average value and month as axis