Clustered column chart with cumulative sum measure based on filters applied on chart

[Anonymous]

Dear all,

I am quite new on Power BI and have been struggling a lot to fix the below issue. I have spent hours and still cannot find a solution. Maybe is it my database that is not suitable for that?

The issue: I have a database loaded into PBI and the database has the below format. I would like to create a clustered bar chart including a measure that is the running total of the selected indicators (line and clustered bar chart). In this case, I am interested by the Financial Indicator "NCF", in a "Poor Economy" and "Good Economy" scenario for the years 2023 and 2024. I would like the chart to show a running total for these two scenario see line on chart below.

The issue is the following. when I apply the running total DAX formula I have created, it does compute the running total of both the "Poor Economy" and "Good Economy" together. In other terms, my measure does not split the calculation by the Economic scenario and assumes it is a single scenario. I therefore end up with a single running total line which is wrong.

My DAX formula is the following: 

CumulativeTotal = CALCULATE(

     SUM(TableQuery[Value]),

     'Calendar'[Date] <= MAX('Calendar'[Date])

)

Does someone has an idea on how to make the running total formula filter for many column? (in this case, filter the financial indicator for NCF, economic scenario for "good" and "poor", and years for 2023 and 2024)? I obviously have thousands of data on 50+ years and 30+ financial indicators. Therefore, I think that any DAX formula including filters within brackets (e.g. filter "NCF") are not going to work as my database is big and I can add/remove some indicators from time to time

Thanks a LOT in advance for the help, this goes beyond my current knowledge. I can add some details if needed just let me know

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

Sorry for that did not know we could add a table, here you go and thanks a lot in advance 🙂

Let me know if something more is missing and I'll add it!

Financial Indicator	Economic Scenario	Date	Plant Name	Value
NCF	Poor Economy	31/12/2023	Plant 1	10
NCF	Poor Economy	31/12/2024	Plant 1	15
NCF	Good Economy	31/12/2023	Plant 1	20
NCF	Good Economy	31/12/2024	Plant 1	25
NCF	Poor Economy	31/12/2023	Plant 2	100
NCF	Poor Economy	31/12/2024	Plant 2	110
NCF	Good Economy	31/12/2023	Plant 2	120
NCF	Good Economy	31/12/2024	Plant 2	130
Costs	Poor Economy	31/12/2023	Plant 1	1
Costs	Poor Economy	31/12/2024	Plant 1	3
Costs	Good Economy	31/12/2023	Plant 2	5
Costs	Good Economy	31/12/2024	Plant 2	7

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

To create a running total for a specific financial indicator, economic scenario, and years, you can use the following DAX formula:

CumulativeTotal = 
CALCULATE(
    SUM(TableQuery[Value]),
    FILTER(
        ALL(TableQuery),
        TableQuery[Financial Indicator] = "NCF" &&
        TableQuery[Economic Scenario] IN {"Poor Economy", "Good Economy"} &&
        TableQuery[Date] IN {DATE(2023,1,1), DATE(2024,1,1)} &&
        TableQuery[Plant Name] = SELECTEDVALUE(TableQuery[Plant Name])
    ),
    'Calendar'[Date] <= MAX('Calendar'[Date])
)

This formula uses the FILTER function to apply multiple filters to the table, including the financial indicator, economic scenario, years, and plant name. The SELECTEDVALUE function is used to ensure that the plant name filter is applied correctly.

You can then use this measure in your clustered bar chart to show the running total for the selected indicators. 

Best Regards,

Stephen Tao

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