Forum Discussion
Clustered column chart with cumulative sum measure based on filters applied on chart
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:
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.
3 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Sorry 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 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.
