Waffle Chart

Waffles....Yum Yum ......Now, before it makes me feel hungry, let’s explore the waffle chart.

What Is A Waffle Chart?

A waffle chart visualizes the part-to-whole relationship and is named after its similarity to the grid-like pattern of a waffle. It is mainly used when composing parts of a whole or comparing progress against a goal. Typically, these charts are square-shaped and composed of a 10x10 grid of squares, where each square represents 1%, and the entire chart represents 100%. Colored squares within the chart indicate the percentage of a given value, with the number of shaded squares corresponding to the percentage being represented. E.g. if 23% of the population liked sushi, then 23 of the 100 squares would be shaded differently than the rest. Waffle charts can display a single category or multiple categories, and multiple waffle charts can be combined to compare different sets of data.

This chart gives a precise idea of the following:

• What elements, combined, create the whole?

• What percentage of the total is each segment?

• How do the segments compare to each other?

Use Case 1: All-in-one Waffle Chart

Objective

Build a Waffle chart to show the sales percentage for each "Category" in a single graph, to better understand each category's composition.

Data Source

We use two following data sources to build the waffle chart:

1) Sample-Superstore

2) Waffle.xlsx, a waffle chart template created as explained below

You can use Excel or any other spreadsheet program to create the template. We should create a table where each cell represents a square on the waffle chart. Label each grid with its Row and Column index and assign a value expressed as a percentage.

I have created a 10x10 grid in Microsoft Excel with the following fields:

a) Row - It has values from 1 - 10

b) Column - Each “Row” value is assigned values from 1 - 10, resulting in 100 grids

c) Percentage – Each of the 100 grids represents 1%, thus adding up to a total of 100%

Waffle.xlsx

Download XLSX • 23KB

Follow the step-by-step guidelines to create an all-in-one waffle chart

Step 1: Connect to Sample Superstore, go to a new sheet, and add the Waffle file

Step 2: Convert the “Row” and “Column” into Dimension

Step 3: Drag "Column" into Columns Shelf and "Row" into the Rows Shelf

Step 4: From the marks card, right-click and select the “Bar”

Step 5: Create a dummy axis for each of the column values using an ad-hoc calculation AVG(1) in the "Columns" shelf

Step 6: Edit the axis to set the range from 0 to 1

Step 7: Select “Fixed “ -> enter the “Fixed start” as 0 and the “Fixed end” as 1

Step 8: Adjust the size of the grid using the “Size” in the Marks card

Step 9: Add Borders for each rectangle. From the "Color" Marks card, select "Border" and apply the color of your choice. (White is selected here)

Step 10: Reduce the width of the column axis to make it look like a square in the waffle

Step 11: Remove the headers. Right Click on AVG(1) and uncheck “Show Header”

Step 12: Remove headers for the "Columns" and "Rows," too. Now it resembles a waffle

Step 13: Sort the "Row" in Descending order to start from the bottom left corner of the grid. Right-click on "Row", click Sort, and select "Descending"

Step 14: Switch to the previous data source, "Sample superstore," and create calculated fields to calculate the sales ratio for each category

Step 15: Create a calculated field "Furniture%" to calculate the percentage of the sales for the "Furniture"

category

Formula: Sum(IF [Category] = 'Furniture' THEN [Sales] END)/Sum([Sales])

Step 16: Create a calculated field "Technology%" to calculate the percentage of the sales for the "Technology" category

Formula: Sum(IF [Category] = 'Technology' THEN [Sales] END)/Sum([Sales])

Step 17: Create a calculated field "Office%" to calculate the percentage of the sales for the "Office"

category

Formula:Sum(IF [Category] = 'Office Supplies' THEN [Sales] END)/Sum([Sales])

Step 18:Switch to the "Waffle" data source. Create a calculated field, "ColorSquare," to give distinct colors to each category and compare the sales ratio of individual types against an index of 1-100 in the "Percentage" measure.

Logic :

IF AVG(Percentage) <= Furniture% THEN 'Furniture'

ELSE AVG(Percentage) <= Furniture% + Office% THEN 'Office'

ELSE 'Technology'

END

Step 19: Add the “ColorSquare” to the “Color” in the marks card

Step 20:Format the chart and legend title as Waffle Chart and Category, respectively. We are done!

Use Case 2: Individual Waffle Charts

Objective

To create individual waffle charts to compare the profit percentage of the categories using Sample Super set data.

Dataset

1) Sample-Superstore

2) Waffle.xlsx, a waffle chart template discussed in the above section

Step-by-step procedure

Step 1 -13: Go to a new Sheet and follow the steps from 1-13 from the above all-in-waffle chart. Now we have a waffle grid. Then continue with the following steps :

Step 14: Switch to the previous data source, "Sample superstore," and create calculated fields to calculate the profit ratio for each category

Step 15: Create a calculated field "FurnitureProfit%" to calculate the percentage of the profit for the "Furniture" category

Formula: Sum(IF [Category] = 'Furniture' THEN [Profit] END)/Sum([Profit])

Step 16: Create a calculated field "TechnologyProfit%" to calculate the percentage of the profit for the "Technology" category

Formula: Sum(IF [Category] = 'Technology' THEN [Profit] END)/Sum([Profit])

Step 17: Create a calculated field "OfficeProfit%" to calculate the percentage of the profit for the "Office" category

Formula: Sum(IF [Category] = 'Office Supplies' THEN [Profit] END)/Sum([Profit])

Step 18: Switch to the "Waffle" data source. Create a new calculated field, "ColorFurnitureProfit" to give a distinct color to the profit percentage of the "Furniture" category in the grid

Logic :AVG(Percentage) <= FurnitureProfit%

Step 19: Add the “ColorFurnitureProfit” to the “Color” in the marks card

Step 20: Optionally, edit the color of the squares by using “Color” in the marks card, label the percentage in the center, and add the titles.

Step 21: Similarly, create waffle charts for the "Technology" and "Office" categories in separate sheets

Step 22: Bring them together in a dashboard to compare the values

Step 23: Finally, we have built a dashboard using the all-in-one waffle chart and the individual waffle grids to analyze the sales and profit of the superstore

Conclusion

Waffle charts make it easy to read, interpret, and compare data, as it is possible to estimate approximate proportions simply by counting the cells, even without labels. They can be created either as an all-in-one chart or as individual charts. For instance, we made one chart to analyze the sales composition of each category, and another by combining multiple charts to compare the profit contribution. Ultimately, we created a dashboard to analyze the data more comprehensively.