topic Re: RANKX + Others without calculated column in DAX Commands and Tips

RANKX + Others without calculated column

Anonymous — Mon, 30 Dec 2019 17:08:52 GMT

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.

Anonymous — Tue, 31 Dec 2019 07:11:02 GMT

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.

Anonymous — Tue, 31 Dec 2019 08:35:46 GMT

Thank you for your answer Anonymous.

Will it gives me the "others" category?

Anonymous — Tue, 31 Dec 2019 08:57:32 GMT

share your sample dataset.

Anonymous — Tue, 31 Dec 2019 09:07:50 GMT

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

Anonymous — Tue, 31 Dec 2019 09:34:00 GMT

I can't add an attachment to the message.

But here is some data:

The result should be:

Anonymous — Tue, 31 Dec 2019 09:37:28 GMT

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

Anonymous — Tue, 31 Dec 2019 09:39:48 GMT

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