Forum Discussion

Anonymous12's avatar
Anonymous12
New Member
1 year ago
Solved

Help Needed: Creating a Waterfall Chart with 3 Subtotals (Gross Margin, EBITDA, Net Income)

Hi Community,

 

I’m trying to build a Waterfall chart in Power BI that shows the bridge from Total Revenue → Net Income.

The requirement is to display three subtotals within the flow:

 

Gross Margin

EBITDA

Net Income

 

What I tried

 

I created a supporting table for the steps:

 

Waterfall Steps =

DATATABLE(

    "Step", STRING, "Sort", INTEGER,

    {

        {"Total Revenue", 1},

        {"COGS", 2},

        {"Gross Margin", 3},

        {"SG&A", 4},

        {"EBITDA", 5},

        {"Operating Expense", 6},

        {"Net Income",

7}

    }

 

Then I wrote the following measure to map each step to the right amount:

Waterfall Amount :=

SWITCH(

    SELECTEDVALUE('Waterfall Steps'[Step]),

    "Total Revenue", [Total Revenue],

    "COGS", -[COGS],

    "Gross Margin", [Gross Margin],

    "SG&A", -[SG&A],

    "EBITDA", [EBITDA],

    "Operating Expense", -[Operating Expense],

    "Net Income", [Net

Income]

 

I pulled this into the native Waterfall visual in Power BI.

 

The Issue

 

The subtotals are not working as intended.

Example with sample values:

 

Total Revenue = 100

 

COGS = 20 → Gross Margin should be 80 (Total Revenue – COGS)

 

SG&A = 10 → EBITDA should be 70 (Gross Margin – SG&A)

 

Operating Expense = 5 → Net Income should be 65 (EBITDA – Operating Expense)

 

But in the current setup, the chart doesn’t display Gross Margin, EBITDA, and Net Income correctly as subtotals — instead, it treats them as normal steps.

 

What I need help with

 

How can I correctly configure the Waterfall chart so that Gross Margin, EBITDA, and Net Income appear as proper subtotals instead of just steps?

 

Any guidance or example would be greatly appreciated 

 

Thanks in advance!

 

Need Help
Show and Tell
Tips and Tricks
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi Anonymous12 
    The native Power BI Waterfall chart can only display real totals at the start and end steps.
    To show multiple intermediate subtotals (like Gross Margin, EBITDA, and Net Income as subtotals in the bridge), you must use a custom visual from AppSource this is the best and most practical solution for these type of use cases.

    Step-by-Step Solution

    • Update Your Data Table and Add a ‘Type’ column to flag subtotals.
      • Add a column to clearly indicate which steps are “Subtotal” (Gross Margin, EBITDA, Net Income)
    • Import a Custom Waterfall Visual
      • In Power BI, go to the Visualizations pane, click the three-dot menu (...) → Get more visuals.
      • Search for and import Drill Down Waterfall PRO (by ZoomCharts) or Inforiver Waterfall.
    • Build and Configure the Chart
      • Use your table as the source.
      • Place ‘Step’ on Category and ‘Value’ in Values
      • In the custom visual’s formatting pane, set rows where Type = “Subtotal” (Gross Margin, EBITDA, Net Income) to be real subtotals. Usually, this is a built-in toggle or configuration

    Please try these steps and if you face any chanllanges you can share a sample of your data or your current Power BI file, I’d be happy to create and provide a sample Power BI (.pbix) file that demonstrates how to configure the waterfall chart with proper subtotals for Gross Margin, EBITDA, and Net Income. Feel free to share your data structure or a simplified version, and I’ll assist further!

     

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

    🔗 Curious to explore more? [Discover here].

    Let’s keep building smarter solutions together!



9 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous12 
    The native Power BI Waterfall chart can only display real totals at the start and end steps.
    To show multiple intermediate subtotals (like Gross Margin, EBITDA, and Net Income as subtotals in the bridge), you must use a custom visual from AppSource this is the best and most practical solution for these type of use cases.

    Step-by-Step Solution

    • Update Your Data Table and Add a ‘Type’ column to flag subtotals.
      • Add a column to clearly indicate which steps are “Subtotal” (Gross Margin, EBITDA, Net Income)
    • Import a Custom Waterfall Visual
      • In Power BI, go to the Visualizations pane, click the three-dot menu (...) → Get more visuals.
      • Search for and import Drill Down Waterfall PRO (by ZoomCharts) or Inforiver Waterfall.
    • Build and Configure the Chart
      • Use your table as the source.
      • Place ‘Step’ on Category and ‘Value’ in Values
      • In the custom visual’s formatting pane, set rows where Type = “Subtotal” (Gross Margin, EBITDA, Net Income) to be real subtotals. Usually, this is a built-in toggle or configuration

    Please try these steps and if you face any chanllanges you can share a sample of your data or your current Power BI file, I’d be happy to create and provide a sample Power BI (.pbix) file that demonstrates how to configure the waterfall chart with proper subtotals for Gross Margin, EBITDA, and Net Income. Feel free to share your data structure or a simplified version, and I’ll assist further!

     

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

    🔗 Curious to explore more? [Discover here].

    Let’s keep building smarter solutions together!



  • antfr99's avatar
    antfr99
    Icon for Resolver II rankResolver II
    1 year ago

    Hi Anonymous12 

     

    I suggest the following for your supporting table : 

     

    Waterfall Steps = 
DATATABLE(
    "Step", STRING, "Sort", INTEGER,
    {
        {"Total Revenue", 1},
        {"Cost of Goods Sold", 2},
        {"Gross Margin", 3},
        {"Selling, General, & Admin", 4},
        {"EBITDA", 5},
        {"Operating Expense", 6},
        {"Net Income", 7}
    }
)

     

    followed by this measure for your subtotals : 

     

    Waterfall Amount = 
SWITCH(
    SELECTEDVALUE('Waterfall Steps'[Step]),
    
    
    "Total Revenue", CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Revenue"),
    "Cost of Goods Sold", CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Cost of Goods Sold"),
    "Selling, General, & Admin", CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Selling, General, & Admin"),
    "Operating Expense", CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Operating Expense"),

  
    "Gross Margin",
        CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Revenue")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Cost of Goods Sold"),
    
    "EBITDA",
        CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Revenue")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Cost of Goods Sold")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Selling, General, & Admin"),
    
    "Net Income",
        CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Revenue")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Cost of Goods Sold")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Selling, General, & Admin")
        - CALCULATE(SUM(Data[Amount]), Data[Transaction Type] = "Operating Expense")
)

     

    to get the following : 

     

     

    which is hopefully similar to what you require.  Atttached is the sample file : 

     

    Power BI Sample File 

     

    Hope this helps

    Antonio 

     

    • v-hashadapu's avatar
      v-hashadapu
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi antfr99 , hope you are doing well. We just wanted to let you know that there is no file in the Power BI sample file link you provided. Please re - check and do the needful.

      Thank you.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    HI Anonymous12 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous12 , Thank you for reaching out to the Microsoft Community Forum.

    We find the answer shared by antfr99  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

     

    Thank you antfr99  for your valuable response..

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous12 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.