waterfall by deneb

[majid154a]

Hello,

The community helped me create a waterfall chart using DAX at the following link:
https://community.fabric.microsoft.com/t5/Desktop/Waterfall-Challenge/m-p/4942137#M1456881

The data I’m using is as follows:

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
12	Actual NPAT	6,334,541

The challenges I’m facing:

1. I want the data labels to appear above the boxes.

2. Can the Actual NPAT start from zero? It’s very high as shown in the attached image.

3.   want to set the color of Actual NPAT to #A4D233 and the color of Budget NPAT to #389BBD.

[Olufemi7]

Hi @majid154a,

Since you’re using Deneb (Vega-Lite), all three requirements can be handled directly in the JSON spec.

1.  Show data labels above the bars

Add a text layer and shift it upward using dy:

{
  "mark": {
    "type": "text",
    "dy": -10,
    "fontWeight": "bold"
  },
  "encoding": {
    "text": {"field": "value", "type": "quantitative"},
    "color": {"value": "black"}
  }
}

If labels are still inside the bars, increase the negative dy (e.g., -12 or -15).
 2. Make “Actual NPAT” start from zero

Override the starting position for that category using a calculated field:

{
  "calculate": "datum.cat === 'Actual NPAT' ? 0 : datum.StartValue",
  "as": "AdjustedStart"
}

Then use:

"y":  {"field": "AdjustedStart"},
"y2": {"field": "EndValue"}

This forces Actual NPAT (Net Profit After Tax) to behave as a total bar starting from zero instead of continuing the running total.

3. Set custom colors

Use conditional color encoding:

"color": {
  "condition": [
    {
      "test": "datum.cat === 'Actual NPAT'",
      "value": "#A4D233"
    },
    {
      "test": "datum.cat === 'Budget NPAT'",
      "value": "#389BBD"
    }
  ],
  "value": "#D3D3D3"
}

• Actual NPAT → #A4D233

• Budget NPAT → #389BBD

Others → default gray

Summary Use a text layer with negative dy for labels above bars Override the start value for Actual NPAT with a calculate transform Apply conditional color encoding for category-specific colors That should resolve all three requirements cleanly within Deneb.

[majid154a]

Thank you @Olufemi7 

can yuu put this changes in the code below, becuase there is no background from my side: 

{

  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

  "data": { "name": "dataset" },

  "transform": [

    {

      "calculate": "datum['Cat Short'] == 'Budget' ? 'Budget' : datum['Cat Short'] == 'Actual NPAT' ? 'Total' : datum.value >= 0 ? 'Increase' : 'Decrease'",

      "as": "Type"

    },

    {

      "calculate": "datum['Cat Short'] == 'Budget' || datum['Cat Short'] == 'Actual NPAT' ? 0 : datum['WF Start']",

      "as": "WF_Start_Adjusted"

    },

    {

      "calculate": "datum['WF End']",

      "as": "WF_End_Adjusted"

    }

  ],

  "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_Adjusted",

          "type": "quantitative",

          "axis": { "title": null, "grid": false, "labels": false, "ticks": false }

        },

        "y2": { "field": "WF_End_Adjusted" },

        "color": {

          "field": "Type",

          "type": "nominal",

          "scale": { "domain": ["Budget", "Increase", "Decrease", "Total"], "range": ["#808080", "#00B050", "#C00000", "#0070C0"] },

          "legend": null

        },

        "tooltip": [

          { "field": "cat", "type": "nominal", "title": "Category" },

          { "field": "value", "type": "quantitative", "title": "Amount", "format": ",.0f" },

          { "field": "WF Start", "type": "quantitative", "format": ",.0f" },

          { "field": "WF End", "type": "quantitative", "format": ",.0f" }

        ]

      }

    },

    {

      "mark": { "type": "text", "fontSize": 11, "fontWeight": "bold", "dy": -12 },

      "encoding": {

        "x": { "field": "Cat Short", "type": "ordinal", "sort": { "field": "index" } },

        "y": { "field": "WF_End_Adjusted", "type": "quantitative" },

        "text": { "field": "value", "type": "quantitative", "format": ",.0f" },

        "color": { "value": "black" }

      }

    }

  ]

}

[Olufemi7]

Hi @majid154a,

Thank you for sharing your full spec.

Below is your corrected version with the changes applied directly to your existing structure.

✔ What’s updated

• Actual NPAT starts from zero

• Actual NPAT color = #A4D233

• Budget color = #389BBD

• Labels remain above bars
  
  

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": { "name": "dataset" },

  "transform": [
    {
      "calculate": "datum['Cat Short'] == 'Budget' ? 'Budget' : datum['Cat Short'] == 'Actual NPAT' ? 'Total' : datum.value >= 0 ? 'Increase' : 'Decrease'",
      "as": "Type"
    },
    {
      "calculate": "datum['Cat Short'] == 'Actual NPAT' ? 0 : datum['WF Start']",
      "as": "WF_Start_Adjusted"
    },
    {
      "calculate": "datum['WF End']",
      "as": "WF_End_Adjusted"
    }
  ],

  "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_Adjusted",
          "type": "quantitative",
          "axis": { "title": null, "grid": false, "labels": false, "ticks": false }
        },
        "y2": { "field": "WF_End_Adjusted" },

        "color": {
          "condition": [
            {
              "test": "datum['Cat Short'] == 'Actual NPAT'",
              "value": "#A4D233"
            },
            {
              "test": "datum['Cat Short'] == 'Budget'",
              "value": "#389BBD"
            }
          ],
          "field": "Type",
          "type": "nominal",
          "scale": {
            "domain": ["Increase", "Decrease", "Total"],
            "range": ["#00B050", "#C00000", "#808080"]
          },
          "legend": null
        },

        "tooltip": [
          { "field": "cat", "type": "nominal", "title": "Category" },
          { "field": "value", "type": "quantitative", "title": "Amount", "format": ",.0f" },
          { "field": "WF Start", "type": "quantitative", "format": ",.0f" },
          { "field": "WF End", "type": "quantitative", "format": ",.0f" }
        ]
      }
    },
    {
      "mark": {
        "type": "text",
        "fontSize": 11,
        "fontWeight": "bold",
        "dy": -12
      },
      "encoding": {
        "x": {
          "field": "Cat Short",
          "type": "ordinal",
          "sort": { "field": "index" }
        },
        "y": {
          "field": "WF_End_Adjusted",
          "type": "quantitative"
        },
        "text": {
          "field": "value",
          "type": "quantitative",
          "format": ",.0f"
        },
        "color": { "value": "black" }
      }
    }
  ]
}

If needed, you can slightly increase dy (e.g., -14) to move labels higher.

This version aligns fully with your existing field names and should work correctly in Deneb