Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Finding Maximum measure value in a pivot table row

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?

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
Partner - Contributor II

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

4 Replies
Partner - Creator

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

Contributor III
Author

Would you be able to give more detail plase?

Partner - Contributor II

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

Contributor III
Author

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