Deneb/Vega-Lite can be used to generate a facetted waffle chart , or a small multiples collection of waffle charts.
I was inspired to investigate how Deneb could be leveraged for this chart type after I saw the recent LinkedIn post by Carlos Barboza using the native scatter plot core visual and the common baseline approach to enhance comparisons.
I had originally published a single waffle chart visual using Deneb/Vega-Lite back in July 2022.
I then expanded on this for Deneb Workout #4 from Enterprise DNA (published in April 2023).
https://forum.enterprisedna.co/t/deneb-workout-04-waffle-chart/35739/4
NOTE: If there are issues using the links to eDNA Forum posts above, try using the Forum Search for “Deneb Waffle Chart”.
While the example presented herein uses the core of the workout #4 solution, the facet view composition technique in Vega-Lite was added to produce small multiples. Also, a switch was added to present positive variance percentages above the common baseline and negative below. Finally, a screen widget was added to effect interactivity and allow the user to select the desired sort order.
This example uses a couple of standard Power BI features, including:
0 - Theme:
- a custom JSON theme file to set all visual features (page background colour, visual background colour, font faces (family) and sizes, rounded corners, subtle centered shadow, etc.) except for:
- Year tile slicer shape and rounded corners set via Power BI [Format] pane
- Country table conditional formatting set via measures (below)
NOTE: Thanks to POINT. Consulting and PowerBI.tips for their excellent sites that were extensively used to investigate the required JSON syntax:
https://themegenerator.point-gmbh.com/en/Home
https://themes.powerbi.tips/themes/properties
1 - Calculated Table:
- a disconnected [Series] table with 100 records used to build the waffle values for each selected country using the following DAX code for columns:
Series =
ADDCOLUMNS(
GENERATESERIES( 1, 100 ),
"ID", [Value]
)
Group =
INT( DIVIDE( Series[ID], 100 ) ) + 1
Remainder =
MOD( Series[ID], 100 ) + 1
Type =
VAR _Group = Series[Group]
VAR _Result = CALCULATE(
MAX( Regions[Country] ),
FILTER( ALL( Regions[Country]), [Rank] = _Group )
)
RETURN
_Result
Percent =
VAR _Group = Series[Group]
VAR _Remainder = Series[Remainder]
VAR _Percent = CALCULATE(
[Variance Percent],
FILTER( ALL( Regions[Country]), [Rank] = _Group )
)
VAR _Result = IF( _Remainder <= INT( _Percent * 100 ) + 1, _Percent, -1 )
RETURN
_Result
2 - Measures:
Seven DAX measures (2 sales, 2 rank, 2 variance, and 1 conditional formatting) as follows:
Sales CY =
SUMX( Sales, Sales[Order Quantity] * Sales[Unit Price] )
Sales PY =
CALCULATE( [Sales CY],
DATEADD( Dates[Date], -1, YEAR ) )
Rank =
RANKX( ALL( Regions[Country] ), [Variance],, DESC )
Sales CY Rank =
VAR _Country = SELECTEDVALUE( Regions[Country] )
VAR _CountriesWithSalesCYAndRank =
ADDCOLUMNS(
SUMMARIZE( Regions, Regions[Country] ),
"@SalesCY", [Sales CY],
"@Rank", [Rank]
)
VAR _Result =
CALCULATE(
[Rank],
FILTER( _CountriesWithSalesCYAndRank, Regions[Country] = _Country )
)
RETURN
_Result
Variance =
[Sales CY] - [Sales PY]
Variance Percent =
VAR _Variance = DIVIDE( [Variance], [Sales CY], 0 )
VAR _Result = _Variance * 100
RETURN
_Result
CF Background Variance =
IF( [Variance Percent] >= 0, "#FFFFFF", "#FF000050" )
// [then] value uses normal 6-character HEX code
// [else] value uses 7th and 8th characters to denote 50% opacity
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 “window/rank” transform to create an ordered prefix for the country name for leveraging the default alphabetic sorting built-in to Vega-Lite
- 3x “calculate” transforms to create padded country labels and choose the desired label
- a “joinaggregate/max” transform to determine the last country
- 2x “calculate” transforms to create integer and decimal variance percentages
- 3x “calculate” transforms to create row and column values for grid presentation
- a shared “params” block with:
- radio buttons (name; rank) to select the country sort order
1 - Facet:
- a columnar layout with facet headers only (no title)
- a label expression to extract the country name from the padded country label
- fixed height/width values to effect a 2:1 ratio and create a layout for both positive and negative values
- a “layer” block (within the “spec” block) for the waffle, label, and divider
2 - Waffle:
- a “point” mark using a filled “circle” shape
- a fixed scale (-10 to 10) to create a constant space for both positive and negative values
- muted conditional colours (positive=green, negative=red, grid=light grey, all with 50% opacity)
3 - Label:
- a nested “transform” block with:
- a “filter” transform to restrict the dataset to a single record per facet
- a “calculate” transform to set the Y position for displaying the label text
- a “text” mark with:
- black fill
- white stroke (to mimic a “halo”)
- Vega-Lite formatting to always display a signed value
4 - Divider:
- a nested “transform” block with:
- a “filter” transform to restrict the dataset to a single record per facet and to exclude the last country
- a “rule” mark with fixed X/X2/Y/Y2 values
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
"text": "Power BI Facetted Waffle Chart using Deneb",
"font": "Verdana",
"fontSize": 24,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": [
"Variance Percent of Current Year Sales over Previous Year Sales by Country",
"Data Source: Enterprise DNA Practice Dataset (years adjusted to 2022-2024)"
],
"subtitleFont": "Verdana",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"data": {
"name": "dataset"
},
"transform": [
{
"window": [
{
"op": "rank",
"as": "_reverse_rank"
}
],
"sort": [
{
"field": "Variance Percent",
"order": "descending"
}
]
},
{
"calculate": "pad( datum['_reverse_rank'], 2, '0', 'left' ) + '-' + datum['Country']",
"as": "_country_rank_country_name"
},
{
"calculate": "'00-' + datum['Country']",
"as": "_zero_country_name"
},
{
"calculate": "_country_sort == 'Rank' ? datum['_country_rank_country_name'] : datum['_zero_country_name']",
"as": "_padded_country_label"
},
{
"joinaggregate": [
{
"op": "max",
"field": "_padded_country_label",
"as": "_max_padded_country_label"
}
]
},
{
"calculate": "floor( datum['Variance Percent'] )",
"as": "_variance_percent"
},
{
"calculate": "datum['_variance_percent'] / 100",
"as": "_variance_decimal"
},
{
"calculate": "ceil( datum['ID'] / 10 )",
"as": "_row"
},
{
"calculate": "datum['_variance_percent'] < 0 ? -1 * datum['_row'] : datum['_row']",
"as": "_signed_row"
},
{
"calculate": "datum['ID'] - ( ( datum['_row'] - 1 ) * 10 )",
"as": "_column"
}
],
"params": [
{
"name": "_country_sort",
"value": "Rank",
"bind": {
"input": "radio",
"options": [
"Name",
"Rank"
],
"name": "Country Sort: "
}
}
],
"spacing": 1,
"facet": {
"column": {
"field": "_padded_country_label",
"header": {
"orient": "top",
"title": null,
"labelExpr": "slice( datum.value, 3, 100 )",
"labelFontSize": 14
}
}
},
"spec": {
"width": 200,
"height": 400,
"layer": [
{
"name": "WAFFLE",
"mark": {
"type": "point",
"filled": true,
"size": 160,
"shape": "circle",
"color": {
"expr": "datum['_variance_percent'] > 0 && datum['ID'] <= abs( datum['_variance_percent'] ) ? 'green' : datum['_variance_percent'] < 0 && datum['ID'] <= abs( datum['_variance_percent'] ) ? 'red' : '#E3E3E3'"
},
"opacity": 0.5
},
"encoding": {
"x": {
"field": "_column",
"type": "quantitative",
"axis": null
},
"y": {
"field": "_signed_row",
"type": "quantitative",
"axis": null,
"scale": {
"domain": [
-10,
10
]
}
}
}
},
{
"name": "LABEL",
"transform": [
{
"filter": "datum['ID'] == 1"
},
{
"calculate": "datum['_variance_decimal'] < 0 ? -9 : 10",
"as": "_label_y"
}
],
"mark": {
"type": "text",
"align": "center",
"fill": "black",
"stroke": "white",
"fontSize": 30,
"fontWeight": "bold",
"xOffset": 8,
"yOffset": 12
},
"encoding": {
"text": {
"field": "_variance_decimal",
"type": "quantitative",
"format": "+.0%"
},
"x": {
"datum": 5
},
"y": {
"field": "_label_y",
"type": "quantitative"
}
}
},
{
"name": "DIVIDER",
"transform": [
{
"filter": "datum['ID'] == 1 && datum['_padded_country_label'] < datum['_max_padded_country_label']"
}
],
"mark": {
"type": "rule",
"color": "#C9C9C9"
},
"encoding": {
"x": {
"datum": 11
},
"y": {
"datum": -10
},
"x2": {
"datum": 11
},
"y2": {
"datum": 10
}
}
}
]
}
}
Also included is the development sample PBIX using the Enterprise DNA Practice Dataset, with years adjusted to 2022-2024.
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 Waffle Chart - V5.pbix (1.7 MB)
