Rank in Pivot table

[shruthibk]

Hi, We have a requirement to highlight the top 5 and bottom 5 values in the pivot table. We can use the rank function to get the top 5 but I am unable to understand how to write this function for the cross table format of the pivot table.

I have attached the screenshot of the sample data with the expected output.

I searched before posting but did not get the info.

Thanks in advance.

[Kushal_Chawda]

Do you have any other dimension in pivot table?  If so how would you want to show Top5?

[shruthibk]

 I don't have any other dimension, I just need to highlight the top5 and bottom 5 values.

in the attachment, i have highlighted the top 5 in green and bottom 5 in the amber color the same way I want to show.

[sunny_talwar · Accepted solution]

You might have to use Aggr() function here

Aggr(If(Rank(TOTAL Sum(Measure)) < 6, Green(), If(Rank(TOTAL -Sum(Measure)) < 6, Yellow())), Maanger, Category)

[Kushal_Chawda]

Assuming you have two dimensions of which one is dragged to column. You can write the below expression in background color

Assuming you have Sum(Value) as your expression

=if(sum(Value)>= max(total aggr(sum(Value),Category,Name),4),green(),
if(sum(Value)<= min(total aggr(sum(Value),Category,Name),4),red()))

[Kushal_Chawda]

@sunny_talwar  gotcha!!!

[shruthibk]

Thanks Sunny