Forum Discussion

alexbalazsalex's avatar
alexbalazsalex
Helper IV
8 years ago
Solved

Rankx or Topn

Dear All ,

 

i would need you support to retrive my top customers

 

I have the following table

 

Year monthproduct detailsClientTons
18-Janaa115
18-Janv24
18-Jands113
18-Jandsg134
18-Jansdfs25
17-Decdf114
17-Deczf113
17-Decdz1134
17-Decz25
17-Decdf26
17-Deczf27
17-Decdfz13
17-Novzf114
17-Novzdf113
17-Novzdd24
17-Novfz25
17-Novzf23
17-Novfzd114
17-Novzf14

 

I have different month - clients products tons ect... a lot of info

I want to retrive my top 2 customers from the above for each month or accumultate month if i do the selection.

 

COuld you please help me with this can t get the foruma right ..

 

so let s say i select a business unit and a month to give me in a list top 2 customers with amount next to it  and maybe total under ?

 

Many Thanks,

Alex

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

    Hi alexbalazsalex,

     

    Was my above suggestion helpful to you? If yes, would you please kindly mark it as an answer so that it can benefit more users?

     

    Thanks,

    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi alexbalazsalex,

     

    Please try below measures:

    Total tons =
CALCULATE (
    SUM ( Client_Tons[Tons] ),
    ALLEXCEPT ( Client_Tons, Client_Tons[Year month], Client_Tons[Client] )
)

Rankx =
RANKX (
    ALLEXCEPT ( Client_Tons, Client_Tons[Year month] ),
    Client_Tons[Total tons],
    ,
    DESC,
    DENSE
)

    Insert a table visual and drag corresponding fields to it. If you want to show TopN records, please add [RANKX] into "visual level filters" and set its value to be equal to or less than N.

     

    Best regards,

    Yuliana Gu