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

[frankiekho]

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 each month starts with a Total Backlog pillar(stacked: grey, yellow, green), followed by floating bars for Order Intake(red) and Turnover(green), leading into the next month's Backlog.

is it really feasible to do this in power bi?

or is there an alternative way to achieve this? like alternative chart to represent

[Anonymous]

Hi @frankiekho ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by @MFelix  . If you still require support, please let us know, we are happy to assist you.

Thank you.

[MFelix]

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]

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]

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]

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 · Accepted solution]

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.

[frankiekho]

hi @MFelix the solution is perfect. i have adjusted it to my needs. but how do i add an extra line trend?

i created a measure 

BO_Inner = IF(SELECTEDVALUE('Categories Waterfall'[Category]) = "BO"; [BO_Innerparts])

and put it in secondary line y-axis. the line didnt show up