Deneb/Vega-Lite can be used to generate facetted performance stacks to compare multiple sets of data.
I recently saw a visual produced by the Visual Capitalist, and wanted to see how Deneb might be leveraged to render a similar chart.
https://www.visualcapitalist.com/sp-500-annual-returns-since-1874/
The example presented herein stacks the annual growth (performance) of a financial market index by year and uses the facet view composition technique in Vega-Lite to produce additional sets of performance stacks to enable easy comparison. A screen widget was added to effect interactivity and allow the user to select the desired comparison year, with the selected year being highlighted with a dark border.
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block with subtitle array
- a shared “transform” block with:
- a “calculate” transform to decrement the year (to allow annual growth to be calculated)
- a “window/lag” transform to determine the “close” price for the preceeding year
- a “filter” transform to remove the record with a null previous value from each facetted dataset (i.e., the first record)
- 2x “calculate” transforms to determine the annual growth and growth percent
- 2x “calculate” transforms to determine the growth “bucket” ID and composite label (order:name)
- a “window/count” transform to determine the year’s vertical position in the growth “bucket”
- a shared “params” block with:
- radio buttons to select the comparison year
- (for space reasons, only years from 2024 to 2000 were included)
- radio buttons to select the comparison year
1 - Facet:
- a horizontal layout with facet headers only (no title)
- a “spec” block with:
- a shared “encoding” block with:
- for the X axis, a label expression to extract the “name” only from the composite bucket label
- for the X axis, an expression to calculate the tick size (by extracting the “order” from the composite bucket label)
- a simple Y axis to ensure all marks use the same growth bucket position and descending order
- a custom tooltip (year, close, previous year close, and growth percent)
- a “layer” block for the year rectangle and label
- a shared “encoding” block with:
2 - Year Rectangle:
- a “bar” mark with:
- conditional border colour using the selected year (if year = selected year then black else transparent)
- conditional fill colour using the “redyellowgreen” colour scheme available built-in to Vega-Lite
- 70% opacity
3 - Year Label:
- a “text” mark with:
- conditional colour using the absolute value of the growth bucket minimum percent (if > 20 then white else black)
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
"text": "Power BI Facetted Performance Stacks using Deneb",
"font": "Verdana",
"fontSize": 24,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": [
"Annual Performance of Financial Market Indicies",
"Data Source: https://ca.investing.com/indices; filtered for 1975-2024"
],
"subtitleFont": "Verdana",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"data": {
"name": "dataset"
},
"transform": [
{
// subtract one year so can calculate the growth for the previous year
"calculate": "year( datum['Date'] ) - 1",
"as": "_year"
},
{
"window": [
{
"op": "lag",
"field": "Open",
"as": "_previous_open"
}
],
"groupby": [
"Index"
],
"sort": [
{
"field": "Date",
"order": "ascending"
}
]
},
{
"filter": "datum['_previous_open'] != null"
},
{
"calculate": "( datum['Open'] / datum['_previous_open'] ) - 1",
"as": "_growth"
},
{
"calculate": "datum['_growth'] * 100",
"as": "_growth_percent"
},
{
"calculate": "datum['_growth_percent'] < -25 ? -5 : datum['_growth_percent'] < -20 ? -4 : datum['_growth_percent'] < -15 ? -3 : datum['_growth_percent'] < -10 ? -2 : datum['_growth_percent'] < -5 ? -1 : datum['_growth_percent'] < 0 ? 0 : datum['_growth_percent'] < 5 ? 1 : datum['_growth_percent'] < 10 ? 2 : datum['_growth_percent'] < 15 ? 3 : datum['_growth_percent'] < 20 ? 4 : datum['_growth_percent'] < 25 ? 5 : datum['_growth_percent'] >= 25 ? 6 : null",
"as": "_growth_bucket_id"
},
{
"calculate": "datum['_growth_percent'] < -25 ? '01:< -25' : datum['_growth_percent'] < -20 ? '02:-25 to -20' : datum['_growth_percent'] < -15 ? '03:-20 to -15' : datum['_growth_percent'] < -10 ? '04:-15 to -10' : datum['_growth_percent'] < -5 ? '05:-10 to -5' : datum['_growth_percent'] < 0 ? '06:-5 to 0' : datum['_growth_percent'] < 5 ? '07:0 to 5' : datum['_growth_percent'] < 10 ? '08:5 to 10' : datum['_growth_percent'] < 15 ? '09:10 to 15' : datum['_growth_percent'] < 20 ? '10:15 to 20' : datum['_growth_percent'] < 25 ? '11:20 to 25' : datum['_growth_percent'] >= 25 ? '12:> 25' : null",
"as": "_growth_bucket_label"
},
{
"window": [
{
"op": "count",
"field": "_year",
"as": "_growth_bucket_position"
}
],
"groupby": [
"Index",
"_growth_bucket_id"
],
"frame": [
null,
0
],
"sort": [
{
"field": "_year",
"order": "ascending"
}
]
}
],
"params": [
{
"name": "_selected_year",
"value": "2024",
"bind": {
"input": "radio",
"options": [
2024,
2023,
2022,
2021,
2020,
2019,
2018,
2017,
2016,
2015,
2014,
2013,
2012,
2011,
2010,
2009,
2008,
2007,
2006,
2005,
2004,
2003,
2002,
2001,
2000
],
"name": "Year: "
}
}
],
"spacing": 20,
"facet": {
"column": {
"field": "Index",
"type": "nominal",
"header": {
"title": null,
"labelFontSize": 18
}
}
},
"spec": {
"width": 490,
"height": 550,
"encoding": {
"x": {
"field": "_growth_bucket_label",
"type": "nominal",
"axis": {
"labelAngle": 0,
"labelExpr": "slice(datum.value, 3, 100)",
"labelFont": "Segoe UI",
"labelFontSize": 12,
"tickSize": {
"expr": "toNumber( slice( datum.value, 0, 2 ) ) <= 6 && toNumber( slice( datum.value, 0, 2 ) ) % 2 == 0 ? 8 : toNumber( slice( datum.value, 0, 2 ) ) > 6 && toNumber( slice( datum.value, 0, 2 ) ) % 2 == 1 ? 8 : 24"
},
"title": "Growth (%)",
"titleFont": "Segoe UI",
"titleFontSize": 14
},
"scale": {
"domain": [
"01:< -25",
"02:-25 to -20",
"03:-20 to -15",
"04:-15 to -10",
"05:-10 to -5",
"06:-5 to 0",
"07:0 to 5",
"08:5 to 10",
"09:10 to 15",
"10:15 to 20",
"11:20 to 25",
"12:> 25"
]
}
},
"y": {
"field": "_growth_bucket_position",
"type": "nominal",
"sort": "descending",
"axis": null
},
"tooltip": [
{
"field": "_year",
"type": "quantitative",
"title": "Year"
},
{
"field": "Open",
"type": "quantitative",
"format": ",.2f",
"title": "Close"
},
{
"field": "_previous_open",
"type": "quantitative",
"format": ",.2f",
"title": "Previous Year Close"
},
{
"field": "_growth",
"type": "quantitative",
"format": ".2%",
"title": "Growth"
}
]
},
"layer": [
{
"name": "YEAR_RECTANGLE",
"transform": [
{
"filter": "datum['_previous_open'] != null"
}
],
"mark": {
"type": "bar",
"tooltip": true,
"opacity": 0.7,
"stroke": { "expr": "datum['_year'] == _selected_year ? 'black' : 'transparent' "},
"strokeWidth": 4
},
"encoding": {
"color": {
"field": "_growth_bucket_label",
"scale": {
"scheme": "redyellowgreen"
},
"legend": null
}
}
},
{
"name": "YEAR_LABEL",
"transform": [
{
"filter": "datum['_previous_open'] != null"
}
],
"mark": {
"type": "text",
"color": {
"expr": "abs( datum['_growth_percent'] ) > 20 ? 'white' : 'black'"
}
},
"encoding": {
"text": {
"field": "_year"
}
}
}
]
}
}
Also included is the development sample PBIX using historical monthly open price data for eight market indices downloaded from the investing.com website for the period 1975-01-01 to 2024-12-31 (each index had differing available full years).
https://ca.investing.com/indices
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 - Facetted Performance Stacks - V5.pbix (2.3 MB)