Rank across pivots with table calculations (Community)

View the original community article here

Last tested: Sep 29, 2019
 

We are able to create Rank across pivots with a number of table calculations.

Assuming that we have two dimensions, brand and status, and one measure, let's call it count. We created an explore, in which we have the dimension brand, we pivot on status and have the measure count and now we want to find the brands with the highest count for each status. So how do we get from this:



 

To This:


 

So for this we can use two nice table calculations. The first one is really obvious. We are going to use the rank function on the measure.

rank(${orders.count},${orders.count}

Now we got the rank in each pivot, but we don't get them to get displayed in the right order. So we need a second one to achieve this. Introducing the lookup function...

lookup(row(),${calculation_1},${products.brand})

This allows us to get the value of the brand dimension where the row number matches the rank that we previously created. Therefore we will get the brand that is ranked 1 in each pivot into the first row. We can do the same for the measure so that it will get displayed next to it.

lookup(row(),${calculation_1},${orders.count})

And there we are, we only have to hide the original dimension and measure and we got a beautiful table that is displaying the values ranked in each pivot.