How to get top n values of a measure for each value of a dimension

[m_wazowski]

Hello,

I have a straight table with these 3 columns:

- Store Name

- Ticket_Number

- Sales

Store Name	Ticket_Number	Sales
Store 1	Ticket 1001	100
Store 1	Ticket 1002	300
Store 1	Ticket 1003	400
Store 1	Ticket 1004	350
Store 2	Ticket 2001	500
Store 2	Ticket 2002	800

I'd like to get a straight table where for each Store Name I keep only three rows: the top 3 Tickets that store has made sorted from the highest to lowest.

This is what I'd like to get:

Store Name	Ticket_Number	Sales
Store 1	Ticket 1003	400
Store 1	Ticket 1004	350
Store 1	Ticket 1002	300
Store 2	Ticket 2002	800
Store 2	Ticket 2001	500

Is it possibile to do that?

Thank you very much for helping.

[BrunPierre]

Hi, perhaps as below.

= If(Aggr(Rank(Sum(Sales)),[Store Name],Ticket_Number)<=3, Sales)

[m_wazowski]

Thank you BrunPierre,

I've come up with this solution and it works. Thank you very much for the input.

= If(Rank(Sum([Sales]),4,1)<=3, Sum([Sales]),)

In the table I have as Dimension Store Code, Store Name, Ticket_Number and this measure.

The problem I currrently have though is that If I add to the table others dimensions for example Date, or SalesPerson_name the expression stops working properly...

[BrunPierre]

As in my suggestion, use the Aggr() function to group these key dimensions.

[m_wazowski · Accepted solution]

I've solved this way.

Instead of working on the measure, I've added a new straight table with this new dimension defined by this expression:

=

Aggr(

Aggr(Nodistinct

If(Rank(Sum([Sales]),4,1)<=3, [Ticket_Number])
,[Store Name]
, [Ticket_Number])

,[Ticket_Number])

Now I can add other dimensions and measure in the table and the expression still works.