2023 Week 29 | Power BI: Core Visual Waffle Chart

Initial (Main) Data

• In a new workbook, use ‘Try a sample dataset’ > ‘Load sample data’  to use the base dataset that comes with Power BI.
• Select the ‘financials’ tab, then Load.
• Create a Measure for Sales using a SUM function to add up the ‘Sales’ column. An example of this might be:Sales = SUM( financials[ Sales] )
• Create a measure for total sales. As we’re using a simple model, we can use one like the following:Total Sales = CALCULATE( [Sales], ALL(‘financials’) )
• Create a Sales % of Total measure, which divides sales by total sales. This will be our measure that we use in the waffle chart, e.g.:Sales % of Total = DIVIDE( [Sales], [Total Sales] )

Waffle Data

• Create a new blank query and call this ‘Waffle Data’.
• In the fx bar, create a list of numbers from 1 to 10, e.g.:={1..10}
• Convert this to a table and rename the column to ‘Y Axis’.
• Create a custom column called ‘X Axis’, which is another list of numbers from 1 to 10 (this will be the same formula you use at the beginning to start the ‘Y Axis’ column).
• Expand the ‘X Axis’ column.
• Add an index column, starting from 1.
• Add a custom column called ‘Legend’, which contains a list of two values: ‘Shaded’ and ‘Unshaded’, e.g.:{ “Shaded”, “Unshaded” }
• Expand the ‘Legend’ column.
• You should now have 200 rows in your query, with a duplicate ‘Y-Axis’, ‘X-Axis’ and ‘Index’ value for the ‘Shaded’ and ‘Unshaded’ values in the ‘Legend’ column.
• Ensure that the Index, X-Axis and Y-Axis are all whole number types and the Legend column is text type.

Waffle Measure

We will create a measure that returns a value we can use to filter our scatter plot: 1 means that our data is in scope and should be plotted, whereas 0 means that it should be ignored.

Create a new measure called ‘Is Waffle Data Point’ that stores three variables:

• The maximum of the Index column from the Waffle Data table.

• The comparator value: this is Sales % of Total multiplied by 100.

Note that you will need to think about your business logic here. You should consider if partial values should be rounded up or down to the nearest whole number so that your waffle does not mis-represent your data. To avoid overcomplication in this workout, round this to the nearest whole number.

• The current value of the ‘Legend’ column from the Waffle Data table

• The measure should return a value of 1 if:
  • The maximum index value is less than the comparator value AND the legend value is “Shaded”
  • The maximum index value is greater than or equal to the comparator value AND the legend value is “Unshaded”

• The measure should return a value of 0 otherwise.

Example:

Is Waffle Data Point =

VAR _MaxIndex = MAX ( ‘Waffle Data'[Index] )

VAR _Comparator = ROUND( [Sales % of Total] * 100, 0)

VAR _LegendValue = SELECTEDVALUE ( ‘Waffle Data'[Legend] )

RETURN

SWITCH(

TRUE(),

_MaxIndex <= _Comparator && _LegendValue = “Shaded”, 1,

_MaxIndex > _Comparator && _LegendValue = “Unshaded”, 1,

0

)

Waffle Visual Dataset

• Add a scatter plot to the canvas.
• Add the ‘Index’ column to the Values data role.
• Add the ‘X-Axis’ column to the X Axis data role. Ensure that this is NOT set to ‘Do not summarize’.
• Repeat the above step for the ‘Y-Axis’ column and the Y Axis data role.
• Add the ‘Legend’ column to the Legend data role.
• Add the ‘Is Waffle Data Point’ measure to the ‘Filters on this visual’ in the filter pane.
• Set the filter to ‘Is’ and type ‘1’ into the text box and apply changes.

Testing the Percentage Allocation

• Add a slicer to the page with a suitable attribute, e.g., Segment. Clicking different values, should result in the shaded and unshaded points varying.
• This may be hard to see now but leave one of these set so that the Legend has both ‘Shaded’ and ‘Unshaded’ showing, as we will need them in our visual dataset to continue configuring the visual.

Waffle Visual Properties

• For the x-axis:
  • Set the range minimum to 0.5 and the maximum to 10.5.
  • Turn off the axis title.
  • Turn off the rest of the x axis.
• Repeat the above three steps for the y axis.

• Turn off the legend.

• For the markers:
  • Set them to your desired shape, e.g., square.
  • Set the size to a suitable value. This will depend on the size and aspect ratio of your visual, but it should be quite large relative to the default.
  • Open the Color property so that ‘Shaded’ and ‘Unshaded’ are visible.
  • Set ‘Shaded’ to a suitable preferred colour (you could leave as the default if this is bold enough)
  • Set ‘Unshaded’ to a muted colour, e.g., light grey.

• • You can either turn off the visual title, or use conditional formatting to make a narrative one, e.g., showing the selected category and percentage value.