Hi guys, I am using the function below to sort the top products by volume and across different locations, however when I add location in the Pivot Table I am getting the table1 TopRank:=if(not(isblank([bias])),rankx(ALLSELECTED(IBPData[Product Group]),[Bias],,0),blank()) Table1 Table IBPData Location Product Group Vol Rank A Beer 1000 1 A Wine 950 2 A Water 820 3 B Wine 980 1 B Water 940 2 B Beer 920 3 C Beer 1100 1 C Water 970 2 C Wine 810 3 D Beer 960 1 D Water 450 2 D Wine 320 3 What I'd like to get is the table2 (below), sorting by vol across all the locations: Table2 Table IBPData Location Product Group Vol Rank A Beer 1100 1 A Beer 1000 2 B Wine 980 3 C Water 970 4 D Beer 960 5 A Wine 950 6

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Sorting column

Thread starter Guinaba
Start date Apr 29, 2024

Guinaba

Board Regular

Apr 29, 2024

Hi guys,

I am using the function below to sort the top products by volume and across different locations, however when I add location in the Pivot Table I am getting the table1

TopRank:=if(not(isblank([bias])),rankx(ALLSELECTED(IBPData[Product Group]),[Bias],,0),blank())

Table1

Table IBPData
Location	Product Group	Vol	Rank
A	Beer	1000	1
A	Wine	950	2
A	Water	820	3
B	Wine	980	1
B	Water	940	2
B	Beer	920	3
C	Beer	1100	1
C	Water	970	2
C	Wine	810	3
D	Beer	960	1
D	Water	450	2
D	Wine	320	3