Question

How can I build a waterfall chart with custom subtotal categories that aren’t natively supported?
I need to create a waterfall chart in Power BI, but I require custom subtotal categories that Power BI does not support by default. These subtotals should be inserted at specific points in the chart based on business logic. What is the best approach using DAX or Power Query to manipulate the data structure and include custom subtotal calculations?

Answer 1

To create a custom waterfall chart with subtotal categories in Power BI, perform the following steps:

1. Restructure Data into Custom Subtotals

For the Waterfall breakdown, Power BI requires a Category column.

To achieve this, you will need to modify your dataset through either Power Query or DAX and create rows to suit your custom subtotals.

Sample Table Format:

Step        | Value  | Type  
------------|-------|---------  
Sales       | 5000  | Increase  
Costs       | -2000 | Decrease  
Profit      | 3000  | Subtotal  
Taxes       | -500  | Decrease  
Net Profit  | 2500  | Subtotal  

2. Use DAX to Create Custom Subtotals

• If subtotals are calculated dynamically, create a DAX measure:

CustomSubtotal =  
IF(  
    SELECTEDVALUE(WaterfallTable[Step]) IN {"Profit", "Net Profit"},  
    SUMX(FILTER(WaterfallTable, WaterfallTable[Step] <> "Profit" && WaterfallTable[Step] <> "Net Profit"), WaterfallTable[Value]),  
    SUM(WaterfallTable[Value])  
)

3. Setup the Waterfall Chart in Power BI

Set Category as the x-axis and Value as the y-axis.

Ensure that "Profit" and "Net Profit" are both classified as Total in the settings of the Waterfall Chart.