Dynamic ranking in power bi

Question

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] )
    )
  )
)

Welcome to Stack Overflow! Stack Overflow is not a discussion forum, it is a Question and Answer site where you can ask a specific programming question that can be answered rather than discussed. Please read How do I ask a good question? and What topics can I ask about here? and then edit your question to conform with the site guidelines. Off-topic questions such as this one are routinely closed, but if edited to ask an answerable question, can be re-opened again. Thanks. — NightOwl888
@AlexisOlson The question is - How to get dynamic ranking based on the selection in the slicer. Suppose I select a brand whose sales are not in Top 5, even then if that brand is selected in the slicer, it should be ranked 1st and remaining brands should be ranked regularly (2nd,3rd,4th) and then 'Others' as summed up remaining brands. Please refer to the screenshot link. — Sanjay_S

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-14T16:45:39

This is trickier than I anticipated, but still possible.

First, let's create a calculated column that ranks the brands to be used later.

Rank = RANKX(ALL(Sheet1), Sheet1[Sales])

A key idea here is that you need a separate table to use as your slicer, otherwise you won't be able to see all the brands when you make a selection. Let's define a new table Brands as follows:

Brands = SUMMARIZECOLUMNS(Sheet1[Brand ], Sheet1[Rank])

We will use this table for our slicer.

Next, we create a fairly complex measure to incorporate all the logic we need:

Top5 = 
    VAR BrandRank = SELECTEDVALUE(Brands[Rank])
    RETURN IF(MAX(Sheet1[Brand ]) = "Others",
               CALCULATE(SUM(Sheet1[Sales]), ALL(Sheet1)) -
               CALCULATE(SUM(Sheet1[Sales]), ALL(Sheet1),
                   Sheet1[Rank] >= BrandRank, Sheet1[Rank] < BrandRank + 5),
                IF(COUNTROWS(ALLSELECTED(Brands[Brand ])) = 1,
                    IF(MAX(Sheet1[Rank]) >= BrandRank &&
                       MAX(Sheet1[Rank]) < BrandRank + 5,
                           SUM(Sheet1[Sales]),
                           BLANK()),
                    IF(MAX(Sheet1[Rank]) <= 5,
                           SUM(Sheet1[Sales]),
                           BLANK())))

You can then use this measure in matrices and donut charts.

Dynamic ranking in power bi

1 Answers