Forum Discussion

shamo's avatar
shamo
Frequent Visitor
4 years ago
Solved

TOP 3 achievers

Hello!

 

I have data that looks like this:

 

Product  Sales    Week

A3202212
B3202212
C 202212
D2202212
E 202212
F1202212
A2202216
B 202216
C3202216
D 202216
E1202216
F 202216

 

I want to have three different measures ("First", "Second", "Third") which will respectively show top 3 selling product based on Total Sales. With current data I should get following results from these 3 measures:

 

Since product A sells the most Measure "First" should return: A

Since product B & C sells second most Measure "Second" should return: B; C

And then measure "Third" should return: D

 

Any help is appriciated. Thanks! 

 

/shamo

 

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You can do this with a few measures. Firstly you need a ranking measure,

    Sales Ranking = RANKX( ALL('Table'[Product]), [Total Sales],,,Dense)

    and then to return the top product

    First = CONCATENATEX( FILTER( VALUES('Table'[Product]), [Sales Ranking] =1 ), 'Table'[Product], ", ")

    then the same for second and third

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You can do this with a few measures. Firstly you need a ranking measure,

    Sales Ranking = RANKX( ALL('Table'[Product]), [Total Sales],,,Dense)

    and then to return the top product

    First = CONCATENATEX( FILTER( VALUES('Table'[Product]), [Sales Ranking] =1 ), 'Table'[Product], ", ")

    then the same for second and third