Forum Discussion
Waterfall chart breakdown
Hello Power BI community! I am new to Power BI currently working on a dashboard and I have a requirement to build a waterfall chart.
Below you can find a sample of the data model:
| Site | Type | Year | Total |
| City Name | L&D | 2023 | 2 |
| City Name | Load | 2023 | 38 |
| City Name | Demand | 2023 | 39 |
| City Name | Baseline | 2023 | 45 |
| City Name | Available Capacity | 2023 | 39 |
| City Name | Installed Capacity | 2023 | 47 |
| City Name | L&D | 2024 | 6 |
| City Name | CU | 2024 | 1 |
| City Name | Load | 2024 | 41 |
| City Name | Demand | 2024 | 40 |
| City Name | Baseline | 2024 | 49 |
| City Name | Available Capacity | 2024 | 47 |
| City Name | Installed Capacity | 2024 | 55 |
| City Name | L&D | 2025 | 4 |
| City Name | Load | 2025 | 44 |
| City Name | Demand | 2025 | 44 |
| City Name | Baseline | 2025 | 55 |
| City Name | CU | 2025 | 10 |
| City Name | Available Capacity | 2025 | 51 |
| City Name | Installed Capacity | 2025 | 60 |
| City Name | CU | 2026 | 10 |
| City Name | Demand | 2026 | 48 |
| City Name | Baseline | 2026 | 69 |
| City Name | Available Capacity | 2026 | 70 |
| City Name | Installed Capacity | 2026 | 70 |
In this data model, Baseline for the next year = Baseline for the preceding year + Total for L&D + Total for CU. For example:
Baseline for 2026 is 69 and it equals to Baseline for 2025 (55) + L&D for 2025 (4) + CU for 2025 (10)
I need the waterfall chart to look as following:
In Y-axis of the chart I have the following measure:
Baseline = CALCULATE(SUM('Final Data Model'[Total]), 'Final Data Model'[Type] = "Baseline")
Type column as Breakdown and Year as Category
The chart shows correct numbers for the Baseline as Blue Bars, the issue is I need to show values for CU and L&D as green bars, however for now it shows these values based on the above formula, which is wrong for my case.
P.S. For some years, there might be a difference between the Baseline of the next year and the baseline of the preceeding year. For example:
Baseline for 2024 is 49 and it equals to Baseline for 2023 (45) + L&D for 2023 (2) and there is no any value for CU but there is still remains 2 to reach the 49. And I want to show it in the chart as "OTHER" Type in chart.
Is there is a possibility to build something like this in Power BI?
I would appreciate if you have an idea or can suggest how to built this using different visualisations as well.
Thanks in advance and looking forward to your replies!
Type column as Breakdown and Year as Category
The chart shows correct numbers for the Baseline as Blue Bars, the issue is I need to show values for CU and L&D as green bars, however for now it shows these values based on the above formula, which is wrong for my case.
P.S. For some years, there might be a difference between the Baseline of the next year and the baseline of the preceeding year. For example:
Baseline for 2024 is 49 and it equals to Baseline for 2023 (45) + L&D for 2023 (2) and there is no any value for CU but there is still remains 2 to reach the 49. And I want to show it in the chart as "OTHER" Type in chart.
Is there is a possibility to build something like this in Power BI?
I would appreciate if you have an idea or can suggest how to built this using different visualisations as well.
Thanks in advance and looking forward to your replies!
No RepliesBe the first to reply