top x within top x

[Lowndsy]

I am trying to replicate an Excel sheet which shows top 10 products and the top 3 "deals" for each of those top products using the top 10 filter.

I have this measure set up as a visual filter <= 10:

Handset Rank = rankx(ALL('LATEST MI'[Handset No Colour]),CALCULATE(COUNT('LATEST MI'[Order Id])))

With no extra fields I get what I expected:

Handset Rank	Handset No Colour
1	iphone 5s 32gb
2	samsung galaxy s6 edge
3	samsung galaxy Note 8
4	samsung galaxy s6
5	iphone 6s plus 32gb
6	iphone 6s 128gb
7	iphone 6s 32gb
8	samsung galaxy s7 plus
9	iphone 7 64gb
10	iphone 6s plus 128gb

However, when I add any extra column to the table all the ranks get set to "1" so it just displays everything.

It is probably looking at each product / deal combination as unique, but I am at a bit of a loss how to stop it.
I have seen a couple of people asking the same question but not managed to get any of the solutions to work.

[Zubair_Muhammad · Accepted solution]

Hi @Lowndsy

Whatever extra Column you add in the table, you have to add the same in RANKX table expression as well.

For example suppose you add 'LATEST MI' [Supplier] in the table, you will need to revise the measure as follows

Handset Rank =
RANKX (
    ALL ( 'LATEST MI'[Handset No Colour], 'LATEST MI'[Supplier] ),
    CALCULATE ( COUNT ( 'LATEST MI'[Order Id] ) )
)

[Lowndsy]

Thanks, that definitely had an impact - I can now add more than one colums and it shows them by rank.

The next step is to have one top x ranked within another one. Is that even doable or should I just create two tables and use hard filters to point each one at the right chunk of data?

[Zubair_Muhammad]

Hi @Lowndsy

Could you provide sample dataset what you are trying to achieve?

[Lowndsy]

Something like this - top 3 tariffs for each of the top 10 handsets:

Handset Rank	Handset No Colour
1	iphone 5s 32gb
	34.99 fun tariff on Sprint
	27.99 Business vodafone
	33.49 T-mobile
2	samsung galaxy s6 edge
	34.99 fun tariff on Sprint
	27.99 T-Mobile Xtra
	33.49 T-mobile Essential
3	samsung galaxy Note 8
	39.99 Max tariff on Cellnet
	23.99 Personal vodafone
	36.99 fun tariff on Sprint
	etc

So 30 rows in total