Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.

You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.

Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.

Building the waffle

Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field

Consumer %

ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.

Create similar fields for the other segments

Corporate %

ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Home Office %

ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Then in the Waffle_Template datasource, create the following field

Colour : Consumer

[Orders (Sample – Superstore)].[Consumer %]>=SUM([Percent])

The above calculation will return true when the % Sales for Consumer is greater than or equal to the Percent value.

Repeat and create equivalent fields for the other segments

Colour : Corporate

[Orders (Sample – Superstore)].[Corporate %]>=SUM([Percent])

Colour : Home Office

[Orders (Sample – Superstore)].[Home Office %]>=SUM([Percent])

Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.

To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.

Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.

Name this sheet Consumer.

Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.

Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.

Building the KPIs

On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.

Create a new field

Percent of Total

ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)

format this to percent with 0 dp and add this to the Text shelf.

Create a new field

Segment UPPER

UPPER([Segment])

and add this to the Text shelf.

Format the text and align centrally

Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.

Creating the dashboard

Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.

Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.

My published viz is here.

Happy vizzin’!

Donna