Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Accepted Solutions
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
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
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
