Waterfall Challenge

[majid154a]

Hi Power BI experts,
I have data like this:

index	cat	value
1	Budget NPAT	4,261,532
2	Gross Margin	2,733,696
3	Variable Costs	-280,573
4	3rd Party Cost Recovery	-435,951
5	Other Income	32,571
6	Fixed Costs	1,324,187
7	Support Cost	738,378
8	Depreciation	-664,124
9	Bad Debt Provision	799,823
10	Finance Exp & Inc	-1,537,797
11	Income Tax	-637,201

and I want to create a Waterfall Chart as required. The issue is that I want the Budget category to appear in gray color, not green or red, because it does not represent an increase or decrease. Also, I want it to be the first bar on the left side.

I tried using different charts but I could not get the result I want. In the attached example, the Budget value appears in green, but I need it in a different color.

I found a custom visual that can do this, but it has a watermark or signature from the creator, which does not look good in the final report (with full respect to the creator’s rights).

Is there any practical solution for this? I really tried a lot and could not find the right way.

Preferably, I want a solution that works directly inside Power BI.

[v-pnaroju-msft]

Hi majid154a,

Could you kindly confirm whether you have submitted this as an idea in the Ideas Forum? If so, please share the link here, as it would be helpful for other community members who may have similar feedback.

If you have any further queries, please feel free to reach out to the Microsoft Fabric community.
Thank you.

[v-pnaroju-msft]

Hi majid154a,

Thankyou for your followup.

Based on my understanding of your requirements, we are not fully able to achieve the desired results with the Deneb custom visual due to its inherent limitations. We have explored all available options and provided the most feasible output accordingly.
If you wish to achieve the requirements through the Deneb visual, we request you to contact the respective support team using the following link: Support Info | Deneb
Additionally, if you would like the features to be implemented using the native Power BI Waterfall visual, you may raise a request on the Microsoft Power BI Ideas forum for consideration in future enhancements via the following link: Fabric Ideas - Microsoft Fabric Community

If you have any further queries, please feel free to reach out to the Microsoft Fabric community.
Thank you.

[v-pnaroju-msft]

Hi @majid154a,

Thankyou for the update.

Based on my understanding of the new changes, please find below the steps, screenshot, and the .pbix file attached:

1.Create a calculated column Cat Short as shown below and drag it into the chart in place of the Cat column.

Cat Short =
SWITCH(
Finance[cat],
"Budget NPAT", "Budget",
"Gross Margin", "Gross Margin",
"Variable Costs", "Variable Costs",
"3rd Party Cost Recovery","3rd Party",
"Other Income", "Other Income",
"Fixed Costs", "Fixed Costs",
"Support Cost", "Support Cost",
"Depreciation", "Depreciation",
"Bad Debt Provision", "Bad Debt",
"Finance Exp & Inc", "Finance Exp",
"Income Tax", "Income Tax",
Finance[cat]
)

2.Replace the previous JSON code with the following code:

{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"data": { "name": "dataset" },
"transform": [
{
"calculate": "datum['Cat Short'] == 'Budget' ? 'Budget' : (datum.value >= 0 ? 'Increase' : 'Decrease')",
"as": "Type"
}
],
"layer": [
{
"mark": { "type": "bar" },
"encoding": {
"x": {
"field": "Cat Short",
"type": "ordinal",
"sort": { "field": "index" },
"axis": {
"title": null,
"labelAngle": 0,
"labelFontSize": 11,
"labelPadding": 10
},
"scale": { "padding": 0.4 }
},
"y": {
"field": "WF Start",
"type": "quantitative",
"axis": null
},
"y2": { "field": "WF End" },
"color": {
"field": "Type",
"type": "nominal",
"scale": {
"domain": ["Budget", "Increase", "Decrease"],
"range": ["#808080", "#00B050", "#C00000"]
},
"legend": null
},
"tooltip": [
{ "field": "cat", "type": "nominal", "title": "Category" },
{ "field": "value", "type": "quantitative", "title": "Amount", "format": ",.0f" }
]
}
},
{
"transform": [{ "filter": "datum.Type == 'Budget'" }],
"mark": {
"type": "text",
"fontSize": 11,
"fontWeight": "bold",
"dy": -6
},
"encoding": {
"x": { "field": "Cat Short", "type": "ordinal", "sort": { "field": "index" } },
"y": { "field": "WF End", "type": "quantitative" },
"text": { "field": "value", "type": "quantitative", "format": ",.0f" },
"color": { "value": "black" }
}
},
{
"transform": [{ "filter": "datum.Type == 'Increase'" }],
"mark": {
"type": "text",
"fontSize": 11,
"fontWeight": "bold",
"dy": -6
},
"encoding": {
"x": { "field": "Cat Short", "type": "ordinal", "sort": { "field": "index" } },
"y": { "field": "WF End", "type": "quantitative" },
"text": { "field": "value", "type": "quantitative", "format": ",.0f" },
"color": { "value": "black" }
}
},
{
"transform": [{ "filter": "datum.Type == 'Decrease'" }],
"mark": {
"type": "text",
"fontSize": 11,
"fontWeight": "bold",
"dy": -6
},
"encoding": {
"x": { "field": "Cat Short", "type": "ordinal", "sort": { "field": "index" } },
"y": { "field": "WF Start", "type": "quantitative" },
"text": { "field": "value", "type": "quantitative", "format": ",.0f" },
"color": { "value": "black" }
}
}
]
}

3.Output screenshot:

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[majid154a]

Great job on this excellent work, and thank you for the support, which I truly appreciate.

I noticed that the total value (the sum of all values) is not displayed on the chart. It should appear on the right side of the chart, and it should be colored green when the value is positive and red when it is negative.

Regarding the X-axis (category values), could you please provide a proper solution to prevent labels from overlapping when the chart is resized smaller, so that the labels remain clear and readable?

Also, concerning the boxes in the chart, is it possible to move them slightly downward? I will later add a comparison between Actual and Budget values as shown in the attached image. Currently, there is not enough space at the top of the chart, so I need some extra space to be created there.

[v-pnaroju-msft]

Thankyou, @johnbasha33 for your response.

Hi majid154a,

Thankyou for the followup.

As you mentioned, you want to create a Waterfall Chart where the Budget category to appear in gray color, not green or red and you want it to be the first bar on the left side.

Based on my understanding, please follow the below steps which might help to resolve the issue:

1. We have created a sample data as shown in the below screenshot:

2.Created Running Total, WF End and WF Start measures.

3. Imported Deneb chart (custom visual) , from AppSource.  Please refer below screenshot:

4. Click on the edit option and drag required fields.

5. Remove the existing sample json code and replace with the below json code.

6. Refer below json code.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "dataset"},
  "transform": [
    {"calculate": "datum.cat == 'Budget NPAT' ? 'Budget' : (datum.value >= 0 ? 'Increase' : 'Decrease')", "as": "Type"}
  ],
  "mark": {"type": "bar"},
  "encoding": {
    "x": {
      "field": "cat",
      "type": "ordinal",
      "sort": {"field": "index"}
    },
    "y": {
      "field": "WF Start",
      "type": "quantitative"
    },
    "y2": {
      "field": "WF End"
    },
    "color": {
      "field": "Type",
      "type": "nominal",
      "scale": {
        "domain": ["Budget", "Increase", "Decrease"],
        "range": ["#808080", "#00B050", "#C00000"]
      },
      "legend": {"title": "Type"}
    },
    "tooltip": [
      {"field": "cat", "type": "nominal"},
      {"field": "value", "type": "quantitative", "format": ",.0f"}
    ]
  }
}

click on apply changes button.

7.Refer below output screenshot and attached .pbix file:

Note: Power BI does NOT support field value conditional colors for Waterfall charts, it is a limitation. By using Deneb custom visual you can get the desired result.

We hope this information helps to resolve your issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[majid154a]

Dear @v-pnaroju-msft 
Thank you very much for the solution you provided. After testing it myself, I confirmed that it works correctly. I truly appreciate your efforts and support.

There is one remaining point I would like to address. As shown in the attached image, I would like to make the following changes:

1. Hide the values highlighted in the green box.

2. Change the values in the orange box to be horizontal instead of vertical.

3. Display the data labels (numbers) on the category values.

   

   
   Waiting for Your answer and feed back

[majid154a]

@johnbasha33 

any update? 

[johnbasha33]

Hi @majid154a ,

you can do this with the built-in Waterfall visual, no custom visual needed.

1) Make “Budget NPAT” the first bar (left)

Create a sort column and sort cat by it.

• You already have index (1..n).
• In Data view: select cat → Column tools → Sort by column → choose index.

Now the waterfall will follow index, so Budget NPAT (index=1) is first.

2) Make “Budget NPAT” gray (not green/red)

In the built-in Waterfall, the only way to force a neutral color is to mark that bar as a Total.

Steps

1. Add the built-in Waterfall visual
2. Put:
• Category = cat
• Y-axis = value (as number)
3. In the chart, right-click the “Budget NPAT” bar → choose Set as total

Now it will use the Total color (you can set that to gray).

Set the gray color

Format pane → Data colors

• Total = gray
• Increase = green
• Decrease = red

Result: “Budget NPAT” becomes a gray “starting” bar on the left.

Important note (so you’re not surprised)

Marking “Budget NPAT” as Total means it’s treated as an anchor in the running calculation. That’s exactly what you want for a starting budget bar.

If you later also want an ending bar (like “Net” / “Final”) as gray too, you can right-click that bar and Set as total as well.

If “Set as total” is disabled

This usually happens when:

• value is text (because of commas)
• or you’re using a measure that returns non-numeric / has weird context

Fix: ensure value is numeric.
If your data is coming in as text like 4,261,532, create a numeric column:

Value_Num =

VALUE(SUBSTITUTE('Table'[value], ",", ""))

Use Value_Num in the visual.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[majid154a]

Thank you dear for answer @johnbasha33 

I tried clicking on the Budget bar and right-clicking, but there is no option to set it as Total. See the attached.

[johnbasha33]

Hi @majid154a 

Short answer:
You’re using “Sum of value” (implicit aggregation) → that’s why Set as total is missing.

Fix (2 steps):

1️⃣ Create an explicit measure

Amount := SUM ( 'Table'[value] )

2️⃣ Use Amount in the Waterfall (not Sum of value)

➡️ Now right-click Budget bar → “Set as total” will appear.

Why:
Power BI only enables Set as total when the Y-axis uses a measure, not an auto-aggregated column.

That’s it.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[majid154a]

Dear @johnbasha33  Thank you for the secnoed help. 

I used measure  as you suggested instead of the aggregate column, but the set as total  still does not appear. Please see the attached

Can you do in your PBI and share it if its work.