Waterfall Challenge

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 !!

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.

johnbasha33 

any update? 

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.

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.

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.