Forum Discussion

Sanjay_S's avatar
Sanjay_S
Frequent Visitor
8 years ago
Solved

Dymanic Rank based on Slicer selection

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

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    Sanjay_S,

     

    You may refer to the measure below.

    Rank =
VAR b1 =
    SELECTEDVALUE ( Sheet1[Brand] )
VAR b2 =
    SELECTEDVALUE ( Table2[Brand] )
RETURN
    IF (
        b1 = b2,
        1,
        RANKX (
            FILTER ( SUMMARIZE ( ALL ( Sheet1 ), Sheet1[Brand] ), Sheet1[Brand] <> b2 ),
            [SumSales],
            ,
            DESC,
            SKIP
        )
            + 1
    )

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Sanjay_S,

     

    You may refer to the measure below.

    Rank =
VAR b1 =
    SELECTEDVALUE ( Sheet1[Brand] )
VAR b2 =
    SELECTEDVALUE ( Table2[Brand] )
RETURN
    IF (
        b1 = b2,
        1,
        RANKX (
            FILTER ( SUMMARIZE ( ALL ( Sheet1 ), Sheet1[Brand] ), Sheet1[Brand] <> b2 ),
            [SumSales],
            ,
            DESC,
            SKIP
        )
            + 1
    )