Returning nth Ranked Item in a card

[Anonymous]

Hi, I have ranked my suppliers by sales with this RANKX function:

Supplier Rank = RANKX(ALL(Table[Supplier]),[Sales])

I want to be able to put the nth ranked item in a card (so essentially filtering on the RANKX measure). I can't create the rank in the table because I want the card to change depending on a location filter.

I tried this formula:

Top Supplier = CALCULATE(FIRSTNONBLANK(Table[Supplier],Table[Supplier]), FILTER(Table,[Supplier Rank] = 1))

but this just returns the first supplier alphabetically.

Any help would be appreciated.

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this measure.

Top Supplier = 
    TOPN( 1, ALL( Table[Supplier] ), [Sales] ) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

I have done something similar in my own workbook, and I have used these two measures:

Rank sales= 
VAR __items =
CALCULATE(
    COUNTROWS( Sales);
    FILTER(
        ALL( FactSales[Sales]);
        NOT( ISBLANK( [Sales (total)]))
    )
)
RETURN
IF(
    RANKX(
        ALL(FactSales);
        [Sales (total)]
    ) > __items;
    BLANK();
    RANKX(
        ALL(FactSales);
        [Sales (total)]
    )
)

title sales(top 1) = 
    CALCULATE(
        VALUES( FactSales[Sales]);
        FILTER(
            FactSales;
            [Rank sales] = 1
        )
    )

If it works then please mark it as the accepted solution.

[Anonymous]

Sorry - I have changed my code slightly and I have made some translation errors... It is not the FactSales table but the DimSalesItem table which I count and [Sales (total)] return the sum of sales from the FactTable.

The countrows return the number of items where there is a sale. This is done to ensure that only items with a sale get a rank, but this is probably not necessary in your case.

The measures should therefore be  (without the blank part):

RANKX(
    ALL(DimSalesItem);
    [Sales (total)]
)

title sales(top 1) = CALCULATE( VALUES( DimSalesItem[Item]); FILTER( DimSalesItem; [Rank sales] = 1 ) ) 

[Anonymous]

Sorry that formula didn't seem to work, I get a can't display visual error. Could you explain the logic behind what it is trying to do?

Thanks