Add Rank (as tale calculation), across pivot table

Hello,
I’m trying to create a heatmap using the simple table,
but I want to hide the actual values from the table itself.

Therefore I would like to create a rank of these values.
The data table has as

  • Rows: hour of the day
  • Columns: Day of the week
  • Values: Orders

I would like to create a rank, that is not just of the column itself, but of the whole table.
how can I do it ?

I’m not sure you can rank across a whole table when using pivoted data, but you could achieve the same thing (a heatmap) by calculating the % of the grand total for each cell in the table.

  1. Create a table calculation to calculate the grand total:
pivot_index(sum(${your_measure_name}),1)+
pivot_index(sum(${your_measure_name}),2)+
pivot_index(sum(${your_measure_name}),3)+
pivot_index(sum(${your_measure_name}),4)+
pivot_index(sum(${your_measure_name}),5)+
pivot_index(sum(${your_measure_name}),6)+
pivot_index(sum(${your_measure_name}),7)
  1. Create a table calculation to calculate the % of the grand total for each cell in the table:
${your_measure_name}/${grand_total}
  1. Hide the base measure from the visualisation

  2. Apply conditional formatting to the table:

This approach also means that you can show the magnitudinal differences e.g. if rank 1 = 13,457, rank 2 = 212, rank 3 = 210 then showing the % of the grand total will reflect that rank 1 is significantly higher than rank 2 and rank 3.

amazing, thank you!