Let's start with a little riddle.
Let's see how much the Brits love their tea
What do you think is the percentage of Brits who drink tea regularly?
And now, have you gotten the same answer with the same accuracy, speed, and confidence level?
Why is it more challenging to get an accurate answer from pie/donut charts and why do we feel less confident trying to read them?
The main reason is the difficulty in calculating the areas of the pie slices/lengths of the donut arcs.
This is much more challenging and less confident than comparing the areas of rectangular shapes.
For those who want to delve deeper, I've also included a link to a very interesting study:
https://eagereyes.org/blog/2016/a-reanalysis-of-a-study-about-square-pie-charts-from-2009?fbclid=IwA...
A well-designed waffle chart with the necessary supporting elements allows for quick and accurate estimation, eliminating the need to calculate areas.
Unfortunately, the waffle charts in our arsenal do not achieve the same results as the one shown in the image above.
So, what can we do?
We'll use the magic of the matrix in combination with some DAX and conditional formatting.
How will it work?
1. We'll create a 10 x 10 table using DAX.
Waffle table =
Var X_table =
SELECTCOLUMNS(GENERATESERIES(1,10), "X_axis",[Value])// Creating of X axis
Var Y_table =
SELECTCOLUMNS(GENERATESERIES(1,10), "Y_axis",[Value])//Creating of Y axis
Return
GENERATE(X_table,Y_table)
2. In this step, we create a measure that divides our percentage measure (percent_tea, in my case) into tens and units in the first stage.
In the second stage, it scans the matrix we created earlier and fills each selected cell with 1 if the value is greater than 0, and with 0 if there is no value.
Tea % =
var Pct = [percent_tea]
var PctProcesed = ROUNDDOWN(Pct/10,0)// tens
var SpillOver = ROUND((Pct/10-PctProcesed)*10,0)//units
Return
If (SELECTEDVALUE('Waffle table'[y_axis])<=PctProcesed,
1,
if(SELECTEDVALUE('Waffle table'[y_axis]) =PctProcesed+1 && SELECTEDVALUE('Waffle table'[X_axis])<=SpillOver,
"1",
"0"
))
3. Now we have come to the point where we only have design work left:
a. We will create a matrix, and choose the design type: None + switch off the totals
b. We will define conditional formatting for the background of the cells:
A cell with a value of 1 will receive a highlighted color.
A cell with a value of 0 will receive gray.
To Hide the numbers we'll apply the same steps for the fonts :
c. To hide the row and column headers, we will set their color to white.
d. To add separation between the waffle squares, we will use matrix gridlines and color them white.
e. To help users read the chart without counting the squares, we will add labels using text boxes.
I have added labels for the beginning, middle, and end. It is possible to add more labels, for example every 10%.
We are left with the title, which also serves as the legend.
f. Since there is no option to format part of the title of a generic visualization in different colors, we will use a text box here as well.
And finally, all that's left is to group the entire construction, so it stays in place.
Thank you to everyone who made it through the read. Good luck with the implementation!
Link to pbix:
Comments