Finding Maximum measure value in a pivot table row

[ozcano]

Hello

I have a pivot table as in the in the following picture. It shows the number of items (inventory) in each OrdererStoreCode (branch). What I want to do is find the OrderstoreCode which has the maximum of inventory and than change color of cell to let's say yellow. For example, for ItemCode Samsung, I want to find out that Orderstorecode3 has the maxmium of 12 items compared to other stores and show value 12 as color code yellow. . How can I do that?

[ozcano]

Hello

I have a pivot table as in the in the following picture. It shows the number of items (inventory) in each OrdererStoreCode (branch). What I want to do is find the OrderstoreCode which has the maximum of inventory and than change color of cell to let's say yellow. For example, for ItemCode Samsung, I want to find out that Orderstorecode3 has the maxmium of 12 items compared to other stores and show value 12 as color code yellow. . How can I do that?

[LRuCelver]

You can use the Rank() function to identify the max value in a column.

[ozcano]

Would you be able to give more detail plase?

[Bruddah_IZ · Accepted solution]

Hi @ozcano ,

If you use Rank() function as suggested by @LRuCelver and use it as the background expression for the only metric in your pivot table, then you can get the desired result:

Here is the formula to get to the result above:  

Pick(Aggr(Rank(Sum(Inventory)), ItemCode, Branch)
  , Yellow()    // 1st
  , LightBlue() // 2nd
  , LightGray() // 3rd
)

Please let me know if this is exactly what you have been looking for. Otherwise, please clarify what you are struggling with

Good luck,
ilya

[ozcano]

Hello,

This solved the problem. But I have another one. Since Pivot table made up multiple dimensions in terms of rows, I have dimensions ProductClass, ProductFirm in addtion to ItemCode. I want to rank that these rows too. Is It possible? I tried this, but did not work:

Pick(Aggr(Rank(Sum( Inventory)), (ItemCode,ProductClass, ProductFirm), Branch) , RGB(0, 44, 0) , RGB(0, 130, 0) ,RGB(0, 180, 0),RGB(0, 220, 0))

Regards