How can I return only the third highest/lowest category as a measure?

[dataaanana]

[Edited please see sample file here]

Hello! I'm using this measure to get the item that received the most and least ordered:

Items Sold = COUNT(Orders[order_id])

MostSoldItem =

CALCULATE(

    VALUES(Menu[item_name]),

    FILTER(

        ALL(Menu[item_name]),

        [Items Sold] = MAXX(ALL(Menu[item_name]), [Items Sold])

    )

)

LeastSoldItem =

CALCULATE(

    VALUES(Menu[item_name]),

    FILTER(

        ALL(Menu[item_name]),

        [Items Sold] = MINX(ALL(Menu[item_name]), [Items Sold])

    )

)

These measures work, but now I want to find the third item that received the most orders, as well as the third item that received the least orders.

I created a Rank measure to help me achieve this:

ItemRanks = CALCULATE(RANKX(ALL(Menu[item_name]),[Items Sold],,DESC))

But I'm not sure how I can use this measure to help me return what I want. Please help! Thank you.

[Anonymous · Accepted solution]

Hi @dataaanana ,

We can create measures.

rank1 = RANKX(ALL(Menu),[Items Sold],,DESC) 

rank2 = RANKX(ALL(Menu),[Items Sold],,ASC) 

the third item(least) = CALCULATE(MAX('Menu'[item_name]),FILTER(ALL('Menu'),[rank2]=3))

the third item(most) = 
CALCULATE(MAX('Menu'[item_name]),FILTER(ALL('Menu'),[rank1]=3))

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[FreemanZ]

hi @dataaanana , 

try to post some sample data

[dataaanana]

Hello! Here's the sample data: [Edited please see sample file here]

Thank you so much!