Building a clustered column chart using a cumulative Sum function with slicer/filtering capability

[JB7167]

I am looking to build a visualization that plots cumulative monthly spend from Jan-Dec. The month parameters will be on the x axis and the values on the y axis. My data looks like this (it is much longer so I have cropped it). 

My goal is to be able to use a slicer to filter the cumulative monthly spend by sub-division. I run into the issue of the filter option not transalting to the visual once the cumulative formula is built. 

The cumulative formula should work like this: (ie. the cumulative column totals all spend of the prior months.

[Anonymous · Accepted solution]

Hi @JB7167 ,

Do you mean that your cumulative measure doesn't work when you select the slicer? Could you please show DAX of measure and screenshots of the error?

You can also try this DAX firstly.

Cumulative Actuals = CALCULATE(SUM('Table'[Actuals]),ALL('Table'),MONTH('Table'[Date])<=MONTH(MAX('Table'[Date])))

Best regards,

Mengmeng Li

[Ritaf1983]

Hi @JB7167 there are many methods to achieve the wanted results.

Please refer to the linked videos:

https://www.youtube.com/watch?v=u-zW-6s8zL0

https://www.youtube.com/watch?v=srLCYBffKr4

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

[vicky_]

Have you tried using the new visual calculations feature to grab the running sum: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview ?

Based on the documentation, it should work with clustered column charts / matrix visuals.