powerbivega-litevegadeneb
IBCS profit loss waterfall chart in Vega-lite, issues regarding bar positions
I have the following vega-lite code:
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"data": {
"values": [
{"label": "Huur woningen (btw onbelast)", "number": 1, "type": "o", "amount": 77855},
{"label": "Huur bedrijfsruimte (btw belast)", "number": 2, "type": "o", "amount": 115604},
{"label": "Netto-omzet", "number": 3, "type": "", "amount": 193459},
{"label": "Personeelskosten", "number": 4, "type": "k", "amount": -70000},
{"label": "Afschrijvingen", "number": 5, "type": "k", "amount": -4097},
{"label": "Huisvestingskosten", "number": 6, "type": "k", "amount": -5302},
{"label": "Exploitatiekosten", "number": 7, "type": "k", "amount": -40380},
{"label": "Kantoorkosten", "number": 8, "type": "k", "amount": -3279},
{"label": "Verkoopkosten", "number": 9, "type": "k", "amount": -267},
{"label": "Algemene kosten", "number": 10, "type": "k", "amount": -9974},
{"label": "Totale kosten", "number": 11, "type": "s", "amount": -133299},
{"label": "Bedrijfsresultaat", "number": 12, "type": "", "amount": 60160},
{"label": "Financiële baten", "number": 13, "type": "k", "amount": 19076},
{"label": "Resultaat voor belastingen", "number": 14, "type": "", "amount": 41084},
{"label": "Belastingen", "number": 15, "type": "k", "amount": -5814},
{"label": "Resultaat na belastingen", "number": 16, "type": "", "amount": 35270},
]
},
"width": 800,
"height": 450,
"transform": [
{"window": [{"op": "sum", "field": "amount", "as": "sum"}]},
{"window": [{"op": "lead", "field": "label", "as": "lead"}]},
{
"calculate": "datum.type === '' ? 0 : datum.sum - datum.amount",
"as": "previous_sum"
},
{
"calculate": "datum.type === '' ? datum.amount : datum.sum",
"as": "sum"
},
],
"encoding": {
"y": {
"field": "label",
"type": "ordinal",
"sort": null,
"axis": {
"labelAngle": 0,
"title": "",
"labelPadding": 50
}
},
"x": {
"field": "sum",
"type": "quantitative",
"axis": null
}
},
"layer": [
{
"mark": {"type": "bar", "size": 20},
"encoding": {
"x": {
"field": "previous_sum",
"type": "quantitative",
"title": "Amount"
},
"x2": {"field": "sum"},
"color": {
"condition": [
{
"test": "datum.label === 'Begin' || datum.label === 'End' || datum.type === ''",
"value": "#f7e0b6"
},
{"test": "datum.amount > 0", "value": "lightgray"},
{"test": "datum.amount < 0", "value": "darkgray"}
],
"value": "#93c4aa"
}
}
},
{
"mark": {
"type": "rule",
"color": "#404040",
"opacity": 1,
"strokeWidth": 2,
"yOffset": -10,
"y2Offset": 10
},
"encoding": {
"y2": {"field": "lead"},
"x": {"field": "sum", "type": "quantitative"}
}
},
{
"mark": {
"type": "text",
"fontWeight": "bold",
"baseline": "middle",
"dx": {"expr": "datum.amount > 0 ? 5 : -5"},
"align": {"expr": "datum.amount > 0 ? 'left' : 'right'"}
},
"encoding": {
"x": {"field": "sum", "type": "quantitative"},
"text": {"field": "amount", "type": "quantitative"},
"color": {
"condition": [
{"test": "datum.amount > 0", "value": "#82CA06"},
{"test": "datum.amount < 0", "value": "#F50100"}
]
}
}
}
],
"config": {"text": {"fontWeight": "bold", "color": "#404040"}}
}
Which gives the following visual:
However the labels with type "K" are starting at a location far after the type "" subtotals. I would like it to look something like this:
The logic should be that when summing the amounts it should ignore the subtotals of type "".
For example: sum type "o" should create subtotal type "". Then it should subtract from type "" type "k". After it should give a total of all type "k" which is called type "s". Then after it should give a subtotal type "" which is a subtraction of all type "k" from the previous subtotal with type "".
The actual goal I'm trying to recreate is IBCS style profit and loss style chart based on the following image:
I hope I was able to articulate this problem and someone is able to help me.
Solution
Try this:
This row should be negative like this:
{"label": "Financiële baten", "number": 13, "type": "k", "amount": -19076},
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"usermeta": {"embedOptions": {"renderer": "svg"}},
"data": {
"values": [
{"label": "Huur woningen (btw onbelast)", "number": 1, "type": "o", "amount": 77855},
{"label": "Huur bedrijfsruimte (btw belast)", "number": 2, "type": "o", "amount": 115604},
{"label": "Netto-omzet", "number": 3, "type": "", "amount": 193459},
{"label": "Personeelskosten", "number": 4, "type": "k", "amount": -70000},
{"label": "Afschrijvingen", "number": 5, "type": "k", "amount": -4097},
{"label": "Huisvestingskosten", "number": 6, "type": "k", "amount": -5302},
{"label": "Exploitatiekosten", "number": 7, "type": "k", "amount": -40380},
{"label": "Kantoorkosten", "number": 8, "type": "k", "amount": -3279},
{"label": "Verkoopkosten", "number": 9, "type": "k", "amount": -267},
{"label": "Algemene kosten", "number": 10, "type": "k", "amount": -9974},
{"label": "Totale kosten", "number": 11, "type": "s", "amount": -133299},
{"label": "Bedrijfsresultaat", "number": 12, "type": "", "amount": 60160},
{"label": "Financiële baten", "number": 13, "type": "k", "amount": -19076},
{"label": "Resultaat voor belastingen", "number": 14, "type": "", "amount": 41084},
{"label": "Belastingen", "number": 15, "type": "k", "amount": -5814},
{"label": "Resultaat na belastingen", "number": 16, "type": "", "amount": 35270},
]
},
"width": 800,
"height": 450,
"transform": [
{"window": [{"op": "lead", "field": "label", "as": "lead"}]},
{
"calculate": "datum.lead === null ? datum.label : datum.lead",
"as": "lead"
},
{
"calculate": "datum.type === '' ? 0 : datum.type === 's' ? 0 : datum.amount",
"as": "runningTot"
},
{"window": [{"op": "sum", "field": "runningTot", "as": "sumTot"}]},
{
"calculate": "datum.number === 1 ? datum.amount : datum.type === '' ? datum.amount : datum.sumTot",
"as": "sum"
},
{
"calculate": "datum.number === 1 ? 0 : datum.type === '' ? 0 : datum.sumTot - datum.amount",
"as": "previous_sum"
},
{"filter": "datum.lead != null"}
],
"encoding": {
"y": {
"field": "label",
"type": "ordinal",
"sort": null,
"axis": {
"labelAngle": 0,
"title": "",
"labelPadding": 10,
"offset": 1,
"ticks": false,
"labelFontSize": 12,
"domainColor": "#000",
"domainWidth": 1,
"labelFontWeight": {
"expr": "datum.label === 'Netto-omzet' ? 'bold' : datum.label === 'Bedrijfsresultaat' ? 'bold' : datum.label === 'Resultaat voor belastingen' ? 'bold': datum.label === 'Resultaat na belastingen' ? 'bold':datum.label === 'Totale kosten' ? 'bold':'normal'"
},
"labelColor": {
"expr": "datum.label === 'Totale kosten' ? 'red':'black'"
}
},
"scale": {"padding": 0}
},
"x": {"field": "sum", "type": "quantitative"}
},
"layer": [
{
"mark": {"type": "bar", "size": 24},
"encoding": {
"x": {
"field": "previous_sum",
"type": "quantitative",
"title": "Amount",
"axis": null
},
"x2": {"field": "sum"},
"color": {
"condition": [
{
"test": "datum.label === 'Begin' || datum.label === 'End' || datum.type === ''",
"value": "#202020"
},
{"test": "datum.type ==='s'", "value": "#616161"},
{"test": "datum.amount >= 0", "value": "#CECECE"},
{"test": "datum.amount < 0", "value": "#A6A6A6"}
],
"value": "#93c4aa"
}
}
},
{
"mark": {
"type": "rule",
"color": "#000000",
"strokeWidth": 2,
"yOffset": -12,
"y2Offset": 12
},
"encoding": {
"opacity": {"value": 1},
"y2": {"field": "lead"},
"x": {"field": "sum", "type": "quantitative"}
}
},
{
"mark": {
"type": "text",
"fontWeight": "normal",
"baseline": "middle",
"dx": {"expr": "datum.amount >= 0 ? 7 : -7"},
"align": {"expr": "datum.amount >= 0 ? 'left' : 'right'"},
"fontSize": 13
},
"encoding": {
"x": {"field": "sum", "type": "quantitative"},
"text": {"field": "amount", "type": "quantitative", "format": ",.0f"},
"color": {
"condition": [
{"test": "datum.amount >= 0", "value": "#23980A"},
{"test": "datum.amount < 0", "value": "#F50100"}
]
}
}
}
],
"config": {
"text": {"fontWeight": "bold", "color": "#404040"},
"view": {"stroke": null}
}
}