Forum Discussion

Helper I

9 months ago

Solved

How to create a repeating 'Pillar-Bridge-Pillar' Waterfall in Power BI?

This is a chart created by my colleague in power point, manually using stacked barchart and remove the unnecessary bars. I am trying to recreate this 'Pillar-Bridge-Pillar' logic in Power BI, where...

MFelix
9 months ago
Hi frankiekho ,

Based on the specifications I can see 3 options to generate the chart:

Creating Visual Calculations on the visual

Creating new measures

Creating Calculation Groups

Check the details below

This step needs to be done for Visual Calculations and new measures

Create a new table with the values: Pillar, Current and Turnover

This will allow to make the split of the bars

ID column is to make the sorting of the categories

Visual Calculations

Create you bar chart in the following way:

X-Axis:

Date

Category

Y-Axis: (The order is important)

Current BO

Next Month BO

Current OI

Turnover

Add the following Visual Calculations:

Pillar Current BO = IF([Category] = "Pillar", [Current BO]) Pillar Next Month = IF([Category] = "Pillar", [Next Month BO]) White Space = IF([Category] = "Current", [Next Month BO] + [Current BO]) Current OI cat = =IF([Category] = "Current", [Current OI]) Turnover = IF([Category] = "Turnover", [Expected Turnover])

Hide all the measures and keep visible only the visual calculations

Format Bar Colors

Based on your color coding

White Space must have transparency set to 100%

Rename the coluns has you need

Documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

New Measures

Add the following measures:

Current BO Cat = IF(SELECTEDVALUE(Categories[Category]) = "Pillar", [Current BO]) Next Month BO Cat = IF(SELECTEDVALUE(Categories[Category]) = "Pillar", [Next Month BO]) Current OI Cat = IF(SELECTEDVALUE(Categories[Category]) = "Current", [Current OI]) White Space Cat = IF(SELECTEDVALUE(Categories[Category]) in {"Current", "Turnover"}, [Current BO] + [Next Month BO]) Turnover Cat = IF(SELECTEDVALUE(Categories[Category]) = "Turnover", [Expected Turnover])

Create you bar chart in the following way:

X-Axis:

Date

Category

Y-Axis: (The order is important)

Current BO Cat

Next Month BO Cat

White Space Cat

Current OI Cat

Turnover Cat

Bar Colors

Based on your color coding

White Space must have transparency set to 100%

Rename the measures on the visual after making the color changes

Calculation Groups

This will allow you to create the visual without the need for additional measures or new tables however be aware that if you are not used to work with calculations groups after creating them you cannot use implict measures on your visuals.

Create the following measure (only measure you need to add)

Current + Next Month = [Current BO] + [Next Month BO]

Add the following calculation groups (they can be added using the Model view):

Pillar = IF( ISSELECTEDMEASURE([Current BO], [Next Month BO]), SELECTEDMEASURE()) Current = if(ISSELECTEDMEASURE([Current OI], [Current + Next Month]), SELECTEDMEASURE()) Turnover = if(ISSELECTEDMEASURE([Expected Turnover], [Current + Next Month]), SELECTEDMEASURE())

Create you bar chart in the following way:

X-Axis:

Date

Column from the calculation group

Y-Axis: (The order is important)

Current BO

Next Month BO

Current+ next month

Current OI

Expected Turnover

Bar Colors

Based on your color coding

White Space must have transparency set to 100%

Final results for all the 3 options:

Please see file attach with all 3 options.

On a final note if you don't want to show the Pillar, Current, Turnover on the X-axis you can replace those values by the initial letter only or if you want there is a possibility to add only the dates based on a new measure and the usage of the label details. Let me know if you want to have that guide.
Bars.pbix52 KB

MFelix

Super User

9 months ago

Hi frankiekho ,

You can do this using some DAX and some categorization of your calculations.

I was able to get to something similar to this:

This can have some additionals tweeks but if you can provide a sample data in order for the adjustments to be inline with you data I appreciate.

frankiekho

Helper I

9 months ago

Chart Month,Current_BO,Current_OI (Red bar),Next_Month_BO,Expected Turnover(green bar)
2023-10-01,8000,2000,8000,2000
2023-11-01,8000,1900,7200,2700
2023-12-01,7200,1750,6900,2050
2024-01-01,6900,2050,6400,2550
2024-02-01,6400,2300,6000,2700
2024-03-01,6000,2100,5700,2400

My Data Structure: I am starting with three main tables:

Table 1: Backlog (BO) – Monthly snapshots of our total backlog value.
Table 2: Order Intake (OI) – The value of new orders received.
Table 3: Date Table – A neutral calendar table used to connect the two fact tables.

What I have done so far:

To show the "Next Month's" Order Intake alongside the "Current Month's" Backlog, I have physically shifted the dates in my OI Table.
I created a new column in the OI table where I subtract 1 month from the original date (e.g., a November order is now dated as October).
I have joined the Date Table to the Backlog[Date] and the OI[Shifted_Date] using a 1-to-Many relationship.
The Logic & Formula: The Turnover represents what was processed during the transition. My logic for the calculation is:
(Current_BO + Current_OI) - Next_Month_BO = Turnover

MFelix
Super User
9 months ago
Hi frankiekho ,

Thank you for the information one additional question if you allow me, in the image you provided there is also a yellow part on top of the grey/green bar what is that value? Do you need it?

Just to be clear:

Current BO - Grey

Next Months BO - Green (on top of the grey bar)

Current OI - Red Bar - Starts at Next month value

Turnover - Green Bar - Starts at Next month value

Is this correct has I refer I see a yellow part also.
- frankiekho
  Helper I
  9 months ago
  Hi MFelix
  To clarify the visual structure and the logic I am using (in ppt):
  The First Pillar (Total Backlog): The stacked bars you see (Grey, Green, Yellow) all represent the Current Month's Backlog. These are simply sub-categories (e.g., Overall BO, Specific Item A, and Item B). For the sake of the calculation, you can treat this as a single Total BO pillar.
  The Second Bar (Order Intake - Red): This represents the Current OI. Because I have shifted my data, this red bar shows the new orders received that will impact the transition to the next month.
  The Third Bar (Turnover - Green): This is my calculated Turnover. It represents the volume produced/shipped from the 'pool' of the Current Backlog and the new Order Intake.
  The Following Pillar: This is the Next Month’s BO, which represents the remaining status after the Turnover has been subtracted.
  
  I am looking for the best way to represent this 'Movement' in a clustered or waterfall-style format where the relationship between these four values is mathematically consistent across the X-axis."
  - MFelix
    Super User
    9 months ago
    Hi frankiekho ,
    
    Based on the specifications I can see 3 options to generate the chart:
    
    Creating Visual Calculations on the visual
    
    Creating new measures
    
    Creating Calculation Groups
    
    Check the details below
    
    This step needs to be done for Visual Calculations and new measures
    
    Create a new table with the values: Pillar, Current and Turnover
    
    This will allow to make the split of the bars
    
    ID column is to make the sorting of the categories
    
    Visual Calculations
    
    Create you bar chart in the following way:
    
    X-Axis:
    
    Date
    
    Category
    
    Y-Axis: (The order is important)
    
    Current BO
    
    Next Month BO
    
    Current OI
    
    Turnover
    
    Add the following Visual Calculations:
    
    Pillar Current BO = IF([Category] = "Pillar", [Current BO]) Pillar Next Month = IF([Category] = "Pillar", [Next Month BO]) White Space = IF([Category] = "Current", [Next Month BO] + [Current BO]) Current OI cat = =IF([Category] = "Current", [Current OI]) Turnover = IF([Category] = "Turnover", [Expected Turnover])
    
    Hide all the measures and keep visible only the visual calculations
    
    Format Bar Colors
    
    Based on your color coding
    
    White Space must have transparency set to 100%
    
    Rename the coluns has you need
    
    Documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
    
    New Measures
    
    Add the following measures:
    
    Current BO Cat = IF(SELECTEDVALUE(Categories[Category]) = "Pillar", [Current BO]) Next Month BO Cat = IF(SELECTEDVALUE(Categories[Category]) = "Pillar", [Next Month BO]) Current OI Cat = IF(SELECTEDVALUE(Categories[Category]) = "Current", [Current OI]) White Space Cat = IF(SELECTEDVALUE(Categories[Category]) in {"Current", "Turnover"}, [Current BO] + [Next Month BO]) Turnover Cat = IF(SELECTEDVALUE(Categories[Category]) = "Turnover", [Expected Turnover])
    
    Create you bar chart in the following way:
    
    X-Axis:
    
    Date
    
    Category
    
    Y-Axis: (The order is important)
    
    Current BO Cat
    
    Next Month BO Cat
    
    White Space Cat
    
    Current OI Cat
    
    Turnover Cat
    
    Bar Colors
    
    Based on your color coding
    
    White Space must have transparency set to 100%
    
    Rename the measures on the visual after making the color changes
    
    Calculation Groups
    
    This will allow you to create the visual without the need for additional measures or new tables however be aware that if you are not used to work with calculations groups after creating them you cannot use implict measures on your visuals.
    
    Create the following measure (only measure you need to add)
    
    Current + Next Month = [Current BO] + [Next Month BO]
    
    Add the following calculation groups (they can be added using the Model view):
    
    Pillar = IF( ISSELECTEDMEASURE([Current BO], [Next Month BO]), SELECTEDMEASURE()) Current = if(ISSELECTEDMEASURE([Current OI], [Current + Next Month]), SELECTEDMEASURE()) Turnover = if(ISSELECTEDMEASURE([Expected Turnover], [Current + Next Month]), SELECTEDMEASURE())
    
    Create you bar chart in the following way:
    
    X-Axis:
    
    Date
    
    Column from the calculation group
    
    Y-Axis: (The order is important)
    
    Current BO
    
    Next Month BO
    
    Current+ next month
    
    Current OI
    
    Expected Turnover
    
    Bar Colors
    
    Based on your color coding
    
    White Space must have transparency set to 100%
    
    Final results for all the 3 options:
    
    Please see file attach with all 3 options.
    
    On a final note if you don't want to show the Pillar, Current, Turnover on the X-axis you can replace those values by the initial letter only or if you want there is a possibility to add only the dates based on a new measure and the usage of the label details. Let me know if you want to have that guide.
    
    Bars.pbix52 KB

Forum Discussion

How to create a repeating 'Pillar-Bridge-Pillar' Waterfall in Power BI?

Related content

Waterfall Chart

Paginated Report headers not repeating

Waterfall Chart Help

Waterfall with multiple values

Waterfall by HTML Help