Knowledge Drop

Rank across pivots with table calculations

  • 15 June 2021
  • 1 reply
  • 733 views

Userlevel 5

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:

Screen Shot 2021-05-06 at 12.17.18 PM.png

 

To This:
Screen Shot 2021-05-06 at 12.18.31 PM.png

 

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.

 

This content is subject to limited support.                

 

 


1 reply

Userlevel 1

This is pure GOLD!!! Thanks for sharing

Reply