Dynamic Ranking Based on Filter selection

[Sanjay_S]

Hello Guys,

I have a requirement wherein, I need to get Top 5 Brands based on Sales vales in a chart. 

The scenario is as follows:

The sample data is as below

Brand	Sales
H	3500
B	2500
I	2200
A	1500
J	1400
K	900
E	800
F	700
L	650
D	600
C	500
N	200
M	150
G	100
Others	null

Now, the requirement is to always show Top 5 brands based on sales. i.e., Top 4 brands and the 5th brand shown as Others aggregating all the other remaining brands.

When the user selects any brand from the slicer(single selection), that particular brand should be ranked - 1st and as usual the next top 3 brands and last one being 'Others' grouping the remaining.

I have managed to get the top 4 brands and others. But, stuck in getting the dynamic ranking based on the slicer selection.

Please see the below measures I created:

Sum of Sales

SumSales = SUM(Sheet1[Sales])

Rank

Rank = RANKX(ALL(Sheet1[Brand ]),[SumSales])

Top5

Top5 = IF ([Rank] <= 4,[SumSales],
  IF(HASONEVALUE(Sheet1[Brand ]),
    IF(VALUES(Sheet1[Brand ]) = "Others",
       SUMX ( FILTER ( ALL ( Sheet1[Brand ] ), [Rank] > 4 ), [SumSales] )
    ) 
  ) 
)

Regards,

Sanjay

[v-ljerr-msft]

Hi @Sanjay_S,

Based on my test, you can firstly create a new table with a single column of all distinct values of Brand, and make sure there is no relationship between the new table and Sheet1.

Table = DISTINCT(Sheet1[Brand])

Then the formulas below should work in your scenario(use Brand column from the new Table as Slicer).

Rank2 = 
IF (
    HASONEVALUE ( 'Table'[Brand] ),
    VAR selectedSales =
        CALCULATE (
            [SumSales],
            FILTER ( ALL ( Sheet1 ), Sheet1[Brand] = VALUES ( 'Table'[Brand] ) )
        )
    RETURN
        IF (
            [SumSales] <= selectedSales,
            RANKX ( FILTER ( ALL ( Sheet1 ), Sheet1[Sales] <= selectedSales ), [SumSales] ),
            -1
        ),
    RANKX ( ALL ( Sheet1[Brand] ), [SumSales] )
)

Top5 =
IF (
    [Rank2] <= 4
        && [Rank2] > 0,
    [SumSales],
    IF (
        HASONEVALUE ( Sheet1[Brand] ),
        IF (
            VALUES ( Sheet1[Brand] ) = "Others",
            SUMX ( FILTER ( ALL ( Sheet1[Brand] ), [Rank2] > 4 ), [SumSales] )
        )
    )
)

Here is the sample pbix file for your reference. 

Regards

[Sanjay_S]

Hello @v-ljerr-msft

Thanks for your reply. 

I tested your approach and seems like I didn't make the requirement quite clear. Sorry for that.

The requirement is when we select any 'Brand' from the slicer, that Brand should be ranked 1st, which is working in your method. But also, the other Top 3 brands should not be the brands whose Sales are lower than the selected Brand, instead it should be regular Top 3 brands.

For example : In the screenshot where you have selected 'B' as the Brand, 'B' should be the 1st Brand which is working and then it should ideally show 'H,I and A' as the next Top 3 brands.

Please check and let me know, I'll also be trying meanwhile.

Thanks,

Sanjay