Deneb/Vega-Lite can be used to create an Inventory Waterfall chart to view production and sales quantities alongside inventory volume; below the waterfall is a standard column chart with series on/off toggles (inventory, production, sales).
Thanks as always to @Gustaw for his comments and suggestions during design and development.
The example herein presents a daily cluster of columns (starting inventory, production, and sales). Daily sales data were created for illustrative purposes. While date and sales data are provided by Power BI and quarterly production levels interactively selected via screen widgets, all inventory data were calculated within Deneb/Vega-Lite. Days with negative ending inventory are highlighted to identify deficits (backorders).
(NOTE: This example uses the widget layout available within Vega-Lite. Customization of the widgets is a popular Deneb feature request and is being looked at for a future release.)
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block
- a shared “params” block with:
- 3x colour parameters for inventory (grey), production (green), and sales (red)
- 4x numeric range parameters for interactive selection of the quarterly daily production quantity
- 3x checkbox parameters for interactive on/off toggling of the column type in the bottom chart
- a shared “transform” block with:
- a “calculate” transform to determine the daily production using the calendar quarter of the date
- 2x “window/sum” transforms to determine the cumulative production and sales quantities
- a “calculate” transform to determine the daily ending inventory quantity
- a “window/lag” transform to determine the daily starting inventory quantity
- 6x “calculate” transforms to set starting and ending quantities for inventory, production, and sales
- a vertical concatenation (vconcat) block for the custom legend, the waterfall, and the column chart
1 - Legend:
- an inline hard-coded dataset for the data types (inventory, production, and sales)
- an “arc” mark with:
- zero radius to hide the placeholder (pie) chart
- standard Vega-Lite generation of a horizontal legend
- symbol colours set according to the colour parameters
2 - Waterfall:
- an outer “layer” block to set shared elements for:
- a “title” block
- an “encoding” block with:
- a temporal X axis with blank title (to take advantage of the spacing assigned automatically by Vega-Lite)
- a quantitative Y axis with a darker and wider grid line at the zero value and a fixed range of -1000 to 1000
- a custom tooltip with date and signed values for daily starting inventory, daily production, daily sales, and daily ending inventory
- an inner “layer” block with:
- a full-height yellow “bar” mark for the background highlight column filtered for only those days with negative ending sales (and hence, inventory)
- a grey “bar” mark for the daily starting inventory quantity
- a green “bar” mark for the ranged daily production quantity
- a red “bar” mark for the ranged daily sales quantity
3 - Column Chart:
- an outer “layer” block to set shared elements for:
- a “title” block
- an “encoding” block with:
- a temporal X axis with title
- a quantitative Y axis with a darker and wider grid line at the zero value and a fixed range of -1000 to 1000
- a custom tooltip with date and signed values for daily starting inventory, daily production, daily sales, and daily ending inventory
- an inner “layer” block with:
- a grey “bar” mark for the daily starting inventory quantity
- dataset filtered to show only if the inventory toggle is on
- a green “bar” mark for the daily production quantity
- dataset filtered to show only if the production toggle is on
- a red “bar” mark for the daily sales quantity
- dataset filtered to show only if the sales toggle is on
- a grey “bar” mark for the daily starting inventory quantity
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
"text": "Power BI Inventory Waterfall using Deneb",
"font": "Segoe UI",
"fontSize": 32,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": "Data Source: synthetic data",
"subtitleFont": "Segoe UI",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"data": {
"name": "dataset"
},
"params": [
{
"name": "_inventory_colour",
"value": "#969696AA"
},
{
"name": "_production_colour",
"value": "#097C72AA"
},
{
"name": "_sales_colour",
"value": "#B71021AA"
},
{
"name": "_q1_production",
"value": 50,
"bind": {
"input": "range",
"min": 1,
"max": 200,
"step": 1,
"name": "Q1 Production: "
}
},
{
"name": "_q2_production",
"value": 60,
"bind": {
"input": "range",
"min": 1,
"max": 200,
"step": 1,
"name": "Q2 Production: "
}
},
{
"name": "_q3_production",
"value": 70,
"bind": {
"input": "range",
"min": 1,
"max": 200,
"step": 1,
"name": "Q3 Production: "
}
},
{
"name": "_q4_production",
"value": 80,
"bind": {
"input": "range",
"min": 1,
"max": 200,
"step": 1,
"name": "Q4 Production: "
}
},
{
"name": "_show_inventory",
"value": true,
"bind": {
"input": "checkbox",
"name": "Inventory: "
}
},
{
"name": "_show_production",
"value": true,
"bind": {
"input": "checkbox",
"name": "Production: "
}
},
{
"name": "_show_sales",
"value": true,
"bind": {
"input": "checkbox",
"name": "Sales: "
}
}
],
"transform": [
// // *** DEV ***
// {
// "filter": "datum['Day of Year Number'] < 91"
// },
{
"calculate": "quarter( datum['Date'] ) == 1 ? _q1_production : quarter( datum['Date'] ) == 2 ? _q2_production : quarter( datum['Date'] ) == 3 ? _q3_production : quarter( datum['Date'] ) == 4 ? _q4_production : null",
"as": "_production"
},
{
"calculate": "datum['Sales'] * -1",
"as": "_sales"
},
{
"window": [
{
"op": "sum",
"field": "_production",
"as": "_cumulative_production"
}
],
"sort": [
{
"field": "Day of Year Number",
"order": "ascending"
}
],
"frame": [
null,
0
]
},
{
"window": [
{
"op": "sum",
"field": "Sales",
"as": "_cumulative_sales"
}
],
"sort": [
{
"field": "Day of Year Number",
"order": "ascending"
}
],
"frame": [
null,
0
]
},
{
"calculate": "datum['_cumulative_production'] - datum['_cumulative_sales']",
"as": "_day_ending_inventory"
},
{
"window": [
{
"op": "lag",
"field": "_day_ending_inventory",
"as": "_day_starting_inventory"
}
],
"sort": [
{
"field": "Day of Year Number",
"order": "ascending"
}
]
},
{
"calculate": "datum['_day_starting_inventory'] + 0",
"as": "_day_starting_net"
},
{
"calculate": "0",
"as": "_inventory_start"
},
{
"calculate": "datum['_day_starting_inventory'] + 0",
"as": "_inventory_end"
},
{
"calculate": "datum['_inventory_end']",
"as": "_production_start"
},
{
"calculate": "datum['_production_start'] + datum['_production']",
"as": "_production_end"
},
{
"calculate": "datum['_production_end']",
"as": "_sales_start"
},
{
"calculate": "datum['_sales_start'] + datum['_sales']",
"as": "_sales_end"
}
],
"spacing": 0,
"vconcat": [
{
"name": "LEGEND",
"width": 1440,
"height": 20,
"data": {
"values": [
{
"legend_id": 1,
"legend_size": 1,
"legend_label": "Inventory"
},
{
"legend_id": 2,
"legend_size": 1,
"legend_label": "Production"
},
{
"legend_id": 3,
"legend_size": 1,
"legend_label": "Sales"
}
]
},
"mark": {
"type": "arc",
"radius": 0
},
"encoding": {
"theta": {
"field": "legend_size",
"type": "quantitative"
},
"color": {
"field": "legend_label",
"type": "nominal",
"scale": {
"domain": [
"Inventory",
"Production",
"Sales"
],
"range": [
{
"expr": "_inventory_colour"
},
{
"expr": "_production_colour"
},
{
"expr": "_sales_colour"
}
]
},
"legend": {
"orient": "top-left",
"direction": "horizontal",
"title": null,
"offset": 0,
"labelColor": "black",
"labelFont": "Segoe UI",
"labelFontSize": 12,
"labelFontStyle": "italic",
"symbolSize": 200,
"symbolType": "circle"
}
}
}
},
{
"width": 1440,
"height": 600,
"layer": [
{
"name": "TOP_WATERFALL",
"title": {
"anchor": "middle",
"align": "center",
"text": "Waterfall Chart",
"font": "Segoe UI",
"fontSize": 16,
"fontWeight": "bold",
"fontStyle": "normal"
},
"encoding": {
"x": {
"type": "temporal",
"axis": {
"title": " ",
"titleFontSize": 16,
"labelFontSize": 12,
"labelFlush": false
}
},
"y": {
"type": "quantitative",
"axis": {
"title": "Units",
"titleFontSize": 16,
"labelFontSize": 12,
"grid": true,
"gridColor": {
"expr": "datum.value == 0 ? '#000000' : '#E3E3E3'"
},
"gridWidth": {
"expr": "datum.value == 0 ? 2 : 1"
}
},
"scale": {
"domain": [
-1000,
1000
]
}
},
"tooltip": [
{
"field": "Date",
"type": "temporal",
"format": "%d-%b-%Y"
},
{
"field": "_day_starting_inventory",
"type": "quantitative",
"format": "+,.0f",
"title": "Starting Inventory"
},
{
"field": "_production",
"type": "quantitative",
"format": "+,.0f",
"title": "Production"
},
{
"field": "_sales",
"type": "quantitative",
"format": "+,.0f",
"title": "Sales"
},
{
"field": "_day_ending_inventory",
"type": "quantitative",
"format": "+,.0f",
"title": "Ending Inventory"
}
]
},
"layer": [
{
"name": "TOP_HIGHLIGHT_BACKROUND",
"transform": [
{
"filter": "datum['_sales_end'] < 0"
},
{
"calculate": "timeOffset( 'hours', datum['Date'], 0 )",
"as": "_highlight_background_datetime_start"
},
// {
// "calculate": "timeOffset( 'hours', datum['Date'], 23 )",
// "as": "_highlight_background_datetime_end"
// },
{
"calculate": "timeOffset( 'minutes', datum['Date'], 1439 )",
"as": "_highlight_background_datetime_end"
}
],
"mark": {
"type": "bar",
"color": "yellow",
"opacity": 0.3
},
"encoding": {
"x": {
"field": "_highlight_background_datetime_start"
},
"x2": {
"field": "_highlight_background_datetime_end"
},
"y": {
"datum": -1000
},
"y2": {
"datum": 1000
}
}
},
{
"name": "TOP_INVENTORY",
"transform": [
{
"calculate": "timeOffset( 'hours', datum['Date'], 6 )",
"as": "_inventory_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_inventory_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_inventory_datetime"
},
"y": {
"field": "_inventory_start",
"axis": {
"title": "Units",
"gridColor": {
"expr": "datum.value == 0 ? '#000000' : '#E3E3E3'"
},
"gridWidth": {
"expr": "datum.value == 0 ? 2 : 1"
}
}
},
"y2": {
"field": "_inventory_end"
}
}
},
{
"name": "TOP_PRODUCTION",
"transform": [
{
"calculate": "timeOffset( 'hours', datum['Date'], 12 )",
"as": "_production_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_production_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_production_datetime"
},
"y": {
"field": "_production_start"
},
"y2": {
"field": "_production_end"
}
}
},
{
"name": "TOP_SALES",
"transform": [
{
"calculate": "timeOffset( 'hours', datum['Date'], 18 )",
"as": "_sales_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_sales_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_sales_datetime"
},
"y": {
"field": "_sales_start"
},
"y2": {
"field": "_sales_end"
}
}
}
]
}
]
},
{
"width": 1440,
"height": 400,
"layer": [
{
"name": "BOTTOM_COLUMN_CHART",
"title": {
"anchor": "middle",
"align": "center",
"text": "Standard Column Chart",
"font": "Segoe UI",
"fontSize": 16,
"fontWeight": "bold",
"fontStyle": "normal"
},
"encoding": {
"x": {
"type": "temporal",
"axis": {
"title": "Date",
"titleFontSize": 16,
"labelFontSize": 12,
"labelFlush": false
}
},
"y": {
"type": "quantitative",
"axis": {
"title": "Units",
"titleFontSize": 16,
"labelFontSize": 12,
"grid": true,
"gridColor": {
"expr": "datum.value == 0 ? '#000000' : '#E3E3E3'"
},
"gridWidth": {
"expr": "datum.value == 0 ? 2 : 1"
}
},
"scale": {
"domain": [
-1000,
1000
]
}
},
"tooltip": [
{
"field": "Date",
"type": "temporal",
"format": "%d-%b-%Y"
},
{
"field": "_day_starting_inventory",
"type": "quantitative",
"format": "+,.0f",
"title": "Starting Inventory"
},
{
"field": "_production",
"type": "quantitative",
"format": "+,.0f",
"title": "Production"
},
{
"field": "_sales",
"type": "quantitative",
"format": "+,.0f",
"title": "Sales"
},
{
"field": "_day_ending_inventory",
"type": "quantitative",
"format": "+,.0f",
"title": "Ending Inventory"
}
]
},
"layer": [
{
"name": "BOTTOM_INVENTORY",
"transform": [
{
"calculate": "_show_inventory == true ? datetime( 2099, 12, 31 ) : datetime( 1999, 12, 31 )",
"as": "_cutoff_end_date"
},
{
"filter": "datum['Date'] < datum['_cutoff_end_date']"
},
{
"calculate": "timeOffset( 'hours', datum['Date'], 6 )",
"as": "_inventory_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_inventory_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_inventory_datetime"
},
"y": {
"field": "_inventory_end",
"axis": {
"title": "Units"
}
}
}
},
{
"name": "BOTTOM_PRODUCTION",
"transform": [
{
"calculate": "_show_production == true ? datetime( 2099, 12, 31 ) : datetime( 1999, 12, 31 )",
"as": "_cutoff_end_date"
},
{
"filter": "datum['Date'] < datum['_cutoff_end_date']"
},
{
"calculate": "timeOffset( 'hours', datum['Date'], 12 )",
"as": "_production_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_production_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_production_datetime"
},
"y": {
"field": "_production"
}
}
},
{
"name": "BOTTOM_SALES",
"transform": [
{
"calculate": "_show_sales == true ? datetime( 2099, 12, 31 ) : datetime( 1999, 12, 31 )",
"as": "_cutoff_end_date"
},
{
"filter": "datum['Date'] < datum['_cutoff_end_date']"
},
{
"calculate": "timeOffset( 'hours', datum['Date'], 18 )",
"as": "_sales_datetime"
}
],
"mark": {
"type": "bar",
"color": {
"expr": "_sales_colour"
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_sales_datetime"
},
"y": {
"field": "Sales"
}
}
}
]
}
]
}
]
}
Also included is the development sample PBIX using synthetic data developed by the author for illustrative purposes.
The intent of this example was not to provide a finished visual, but rather to explore the use of the Deneb custom visual and the Vega-Lite language within Power BI and to serve as a starting point for further development.
This example is provided as-is for information purposes only, and its use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Example - Inventory Waterfall - V10.pbix (4.7 MB)