topic Re: How can I return only the third highest/lowest category as a measure? in DAX Commands and Tips

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

dataaanana — Wed, 20 Dec 2023 14:20:47 GMT

[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.

FreemanZ — Wed, 20 Dec 2023 02:12:27 GMT

try to post some sample data

dataaanana — Wed, 20 Dec 2023 14:21:26 GMT

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

Thank you so much!

Anonymous — Mon, 25 Dec 2023 07:59:09 GMT

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.