Add Rank (as tale calculation), across pivot table

[Matteo59028]

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 ?

[ruthc · Accepted solution]

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)

2. Create a table calculation to calculate the % of the grand total for each cell in the table:

${your_measure_name}/${grand_total}

3. Hide the base measure from the visualisation

4. 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.

[Matteo59028]

amazing, thank you!