Cumulative sum in card/matrix visuals, interacting to the selection in the cumulative column chart

[michaelm16]

In Power BI, I have a column chart with the following cumulative sum formula, which also takes into account the applied FY slicer:

CALCULATE([Product quantities],FILTER(ALLSELECTED(fact_turnover),fact_turnover[Order Date]<=MAX(fact_turnover[Order Date])))

However, in the card visual and matrix table, the measure does not work as I need it to, and when selecting any month in the column chart, the card and matrix table only displays the sums of values from the selected months. The measure is working only if no column is selected and I am quite new to the DAX, so I don't know how to setup it corectly. 

The card should work as follows: When selecting a column in the column chart, display the cumulative sum that matches the selected column (the cumulative sum up to that month, with the same FY slicer consideration as in the column chart).

The matrix table should work the same as the card, but with a breakdown by Projects from dim_product table.

What DAX measures should I use in the Card and Matrix table visuals?

[v-ssriganesh]

Hello @michaelm16,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @michaelm16,
Thank you for reaching out to the Microsoft Fabric Community Forum. To assist you further, could you please provide sample data that clearly illustrates the issue you're experiencing as mentioned by @Ritaf1983.

Best Regards,
Ganesh singamshetty.

[v-ssriganesh]

Hello @michaelm16,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[v-ssriganesh]

Hello @michaelm16,

Just checking in to see if you had a chance to review my earlier message and share the requested details. Once we have that, we’ll be better equipped to help you further. Thanks.

[michaelm16]

Hello @v-ssriganesh , 

attached down below is the link to my desired output. 
test_data.xlsx

[v-ssriganesh]

Hello @michaelm16,

Thanks for sharing your file. Unfortunately, the Excel file link you attached isn’t opening on my side. Could you please upload the Excel file directly from your local machine to the community post instead of linking it externally? That way, we can all access it easily and reproduce your scenario.

For your reference, I’m attaching a screenshot showing how to attach files in the community.

Also, here’s a helpful guide: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @michaelm16,
Following up to see if you were able to look into my previous response. Providing the requested information will help us move forward with resolving your issue.

Thank you.

[michaelm16]

Hello @v-ssriganesh , 

unfortunately I can't upload my file.

I've tried .xlsv and also .csv, but none of them is working. It shows me ".xxx format isn't supported" everytime. 

Down below is my desired output in text/csv format (the separator is ";"). I hope this format is okay for you. 

Month;FY slicer;Sales;Cumulative sales sum without slicer (column chart);Cumulative sales sum with slicer in Fiscal Year applied (column chart);Cumulative sales sum showing in a card visual, when clicking on a month (column A) in column chart, without slicer in FY applied;Cumulative sum showing in a card visual, when clicking on a month (column A) in column chart, with slicer in FY applied

24-07;2024/2025;155;155;155;155;155;155;155

24-08;2024/2025;740;895;895;895;895;895;895

24-09;2024/2025;804;1699;1699;1699;1699;1699;1699

24-10;2024/2025;416;2115;2115;2115;2115;2115;2115

24-11;2024/2025;384;2500;2500;2500;2500;2500;2500

24-12;2024/2025;973;3473;3473;3473;3473;3473;3473

25-01;2024/2025;972;4446;4446;4446;4446;4446;4446

25-02;2024/2025;868;5314;5314;5314;5314;5314;5314

25-03;2024/2025;876;6191;6191;6191;6191;6191;6191

25-04;2024/2025;398;6589;6589;6589;6589;6589;6589

25-05;2024/2025;425;7015;7015;7015;7015;7015;7015

25-06;2024/2025;355;7371;7371;7371;7371;7371;7371

25-07;2025/2026;570;7941;570;7941;570;7941;570

25-08;2025/2026;909;8851;1480;8851;1480;8851;1480

[v-ssriganesh]

Hello @michaelm16,

I have reproduced your scenario in Power BI Desktop and I’m getting the expected output as per your requirement.

• The Cumulative Sales by Month chart builds correctly month over month.
• The Cumulative Sales Card reflects the overall cumulative sales total.
• The Project-wise Matrix displays cumulative sales for each project, and the grand total shows the cumulative sales at the overall level (this is expected behavior in Power BI since totals are recalculated, not summed from sub-rows).
• The Month-wise Table shows both monthly sales and cumulative sales progression as expected.

For your reference, I am attaching the sample .pbix file.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @michaelm16,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[michaelm16]

Hello @v-ssriganesh , 

thank you for your time. Everything is working correctly, except for the breakdown by project. 
If I click on the first month in your solution, there is Project A and B, but if I click on the second month, there are Projects A and C, which doesn't make sense, as it should be cumulative and it seems like Project B "disappeared" in your solution. 

[v-ssriganesh · Accepted solution]

Hello @michaelm16,

Thank you for pointing that out, you are correct. The reason some projects “disappear” when you click a month is that Power BI only shows categories that have values in the current filter context.

To ensure all projects remain visible across months in the cumulative breakdown, you can try one of these approaches:

• Enable Show items with no data on the Project field in the matrix.
• Or, create a separate dim_project table (e.g: dim_project = DISTINCT(fact_turnover[Project])) and use that field in the matrix. This guarantees all projects are always listed, with cumulative sales shown (0 if no transactions yet).

This will keep Project B visible in your example, even if it has no sales in earlier months.

Best regards,
Ganesh Singamshetty

[Ritaf1983]

Hi @michaelm16 

If the goal is to prevent the selection in the cumulative chart from affecting the card or matrix visuals, you don’t need to change the DAX. This can be done directly in the UI by disabling the visual interaction using "Edit interactions". This will ensure the card and matrix remain unaffected by clicks on the cumulative chart.

Result :

More guidness about the functionality here:
https://www.youtube.com/watch?v=5MteQv_h6ck

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

[michaelm16]

Hi @Ritaf1983,
the thing is, I don't want to disable interactions.
I want both visuals to be dynamic.
Currently, the cumulative sum for the card and matrix table only works if I don't click on any column in the cumulative column chart. If I click on a month in the chart, the result is only correct in the chart, and the other two visuals only show the sums for the one month I clicked on.

I need the Card visual and Matrix table to work as follows:
For example, if I click on the month 24-11 in the cumulative column chart, the card should display the cumulative sum from the first month in the chart (24-07) to the month that is clicked (24-11). The matrix table should work on the same principle, except that it would also be broken down into projects/product categories.

EDIT: In other words: The sums in the card and matrix table should match the currently selected column in the column chart. Unfortunately, this is not the case, because the selected column represents the cumulative sum, while the other visuals only show me the cumulative sums for the selected month, which is not what I want. The formula only works in the column chart, and I have to use a different one for the other two visuals, but unfortunately I don't know how to adjust it correctly, because I'm still learning DAX 🙂

[Ritaf1983]

Hi @michaelm16  again

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[michaelm16]

Hello @Ritaf1983 , 

attached down below is the link to my desired output: 
test_data.xlsx