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

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.