This is definitely going to be a table calculation situation... a while back I made a square type of chart with some similarities, but I had a natural block unit dimension (one part) , while you don't. So I created one ... a new sheet in your table called 'index' with two columns, j: 1,2,3 ... ,100 and join: 1,1,1,....,1 and a new column to your existing data; join 1,1,1,....,1
Then I joined the existing table to the new table on the join column.
now you can drop j in your Detail to be your block unit, and use mod: ([Last]-1) %10 and i: Round((([Last] -1) / 10.0)+0.5,0)
as your x, y coordinates for your shapes.
Shape is driven by a calc: IF ([Spend Increment]*[Last] <= [Spend by Pane] ) THEN 'filled' ELSE 'empty' END
Spend increment is the amount of money in one box, or WINDOW_SUM(SUM([Spends]))/100
Spend by pane is simply WINDOW_SUM(SUM([Spends]))
Color is driven by Media. Market filter just works.
Since there are 100 boxes, the number of filled boxes naturally coincides with the % of total.
changes to your excel file:
my starting point:
Pretty close to what you want:
waffle.twbx 41.9 KB
I really like his method of doing it with 1-10 1-10(2) and a combined field 1-100.