Deneb Example - Facetted Waffle Chart

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)

marking as solved

1 Like

Hi @Greg

fyi the workouts section aren’t there anymore.

My understanding the team is going a different directions for the workouts.

thanks
Keith

Unsure of why you made this comment … the various Deneb examples have never been a part of the eDNA Workouts … the last workout that I was involved in was Deneb Workout #10 posted back in June 2023, and I’m unaware of the current/future workout plans.
Greg

Hi @Greg
When you click on the link you provided nothing comes up, as all the workouts for all area have be removed from the portal.

I hope this helps.

Keith

All links work properly for me, and I can only guess that perhaps their are differences between our accounts … @EnterpriseDNA, please comment and resolve.

In the meantime, you can use the forum search (as I did to get the links) and search for “Deneb Waffle Chart”, and I’ve added a note to the original post accordingly.

Hope this helps.
Greg

i’m talking about this link
https://forum.enterprisedna.co/t/deneb-workout-04-waffle-chart/35739/4

give this error

i

It could be on different between our accounts.

thanks
Keith

Understood … but works fine for me … @EnterpriseDNA please resolve.
Greg

I have the same issue as Keith