cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

RANKX + Others without calculated column

Hi,

 

I have seen a lots of topics talking about it, but every time, it was about creating new tables or calculated columns ; I cannot.

I use DirectQuery mode, and I want to have a pie chart with Top 5 turnover and "Others" category represented.

 

I hope to have not missed the right topic, but here is this one.

 

I already have a rank measure which ranks the cities by turnover.

 

Thank you.

7 REPLIES 7
Highlighted
Anonymous
Not applicable

Re: RANKX + Others without calculated column

Try below measure,

Rank =
var a =RANKX(ALLSELECTED(Table1[Customer]),[Sales],,DESC,Dense)
var b =5 (As you want  top 5 you can make it dynamic also.... top N customers)
return
if(a<=b,1,0)
And add it in visual level filter and set is not 0.
This will give you top 5 Customers without creating any Column.
Highlighted
Helper I
Helper I

Re: RANKX + Others without calculated column

Thank you for your answer @Anonymous.

Will it gives me the "others" category?

Highlighted
Anonymous
Not applicable

Re: RANKX + Others without calculated column

share your sample dataset.

Highlighted
Anonymous
Not applicable

Re: RANKX + Others without calculated column

sorry it will not show others. let me try for others

Highlighted
Helper I
Helper I

Re: RANKX + Others without calculated column

I can't add an attachment to the message.

 

But here is some data:

CountryTurnover
USA100
CANADA200
AUSTRALIA300
GERMANY100
RUSSIA500
LITHUANIA600
UKRAINE400

 

The result should be:

error3.png

Highlighted
Anonymous
Not applicable

Re: RANKX + Others without calculated column

without column in not possible. you need to create calculated column.

Highlighted
Helper I
Helper I

Re: RANKX + Others without calculated column

Ok so no solution for me 😔 as I cannot create calculated columns.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors