Waterfall Challenge

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.