Forum Discussion

majid154a's avatar
majid154a
Helper II
6 months ago

waterfall by deneb

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
1Budget NPAT4,261,532
2Gross Margin2,733,696
3Variable Costs-280,573
43rd Party Cost Recovery-435,951
5Other Income32,571
6Fixed Costs1,324,187
7Support Cost738,378
8Depreciation-664,124
9Bad Debt Provision799,823
10Finance Exp & Inc-1,537,797
11Income Tax-637,201
12Actual NPAT6,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.

Help

3 Replies

  • Olufemi7's avatar
    Olufemi7
    Super User
    6 months ago

    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's avatar
      majid154a
      Helper II
      5 months ago

      Thank you Olufemi7 

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

      {
        "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's avatar
        Olufemi7
        Super User
        5 months ago

        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