Forum Discussion
Clustered column chart with cumulative sum measure based on filters applied on chart
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.
Super UserPlease 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/1447523
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 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.