Ranking for a pivot table which has multiple dimensional rows

[ozcano]

Hello

I have a pivot table as in the in the following picture. It shows the number of items (inventory) in each column (branch). Pivot table is made up multiple dimensions in terms of rows, I have dimensions ProductClass, ProductFirm in addition to ItemCode in the rows. For finding ranking for let’s say the dimensional row ItemCode only, this code for background color expression works fine:

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

But I want to do the ranking for the rows ProductClass, ProductFirm too. That means ranking should work for all the dimensional rows (for ProductClass, ProductFirm in addition to ItemCode).

How can I do that?

Regards

Ozcan

[vinieme12]

can you post some sample data and expected output? it's not clear what you are trying to achieve

[ozcano]

Hello

Basically this is what I achieved with the code I shared in my original post (the darker the green means there is more inventory on that branch compared to other branches per item):

And this is what I want to actually achieve (red rectangles are drawn for directing your attentions only):

I want to color code not only ItemCodes but ProductClass (Such as PHONEs, TVs etc) and Product Firm (Samsung, Xiaomi etc.) in terms of branch distribution. When color coding the aim is to differentiate number of inventories between the branches so that we could find the branch with higher inventory, then lower than that and then lower than that etc. The aim is to find items, ProductClass, ProductFirm that are potentially sitting idle at different branches. So if required they will be transferred to the branches that has more potential to sell that item, item type etc.

Regards, Ozcan

[vinieme12 · Accepted solution]

Use Dimensionality() as the aggregation scope for ranking will differ for each dimension as below

=Pick(

Pick(dimensionality()
,aggr(rank(Sum(Inventory)),ProductClass ,Branch)
,aggr(rank(Sum(Inventory)),ProductClass ,[Product Firm],Branch)
,aggr(rank(Sum(Inventory)),ProductClass ,[Product Firm], ItemCode,Branch)
)

, RGB(0, 44, 0) , RGB(0, 130, 0) ,RGB(0, 180, 0),RGB(0, 220, 0))