cancel
Showing results for
Did you mean:
New Member

## top N customer based on filter

Hi All,

I have a transactional data of all customer sales for a year. I need to filter only the records of the top N ( 10/20/30 based onfilter ) customers based on the month or quater filter i would be having.

Thank you

1 ACCEPTED SOLUTION
Microsoft

@rajeev16

If you have a table like below, you can first create a Calendar table and create relationship between transactional table and Calendar table with Date key.

```Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )```

Then create two Columns in the Calendar table for the Month and Quarter slicer.

```Month =
MONTH ( 'Calendar'[Date] )```
```Quarter =
ROUNDUP ( MONTH ( [Date] ) / 3, 0 )```

Create a measure to rank the customer according to selection in the Month and Quarter slicer.

```Rank =
RANKX ( ALL ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ) ) )```

I’ve also uploaded my .pbix file here for reference.

Best Regards,

Herbert

3 REPLIES 3
Microsoft

@rajeev16

If you have a table like below, you can first create a Calendar table and create relationship between transactional table and Calendar table with Date key.

```Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )```

Then create two Columns in the Calendar table for the Month and Quarter slicer.

```Month =
MONTH ( 'Calendar'[Date] )```
```Quarter =
ROUNDUP ( MONTH ( [Date] ) / 3, 0 )```

Create a measure to rank the customer according to selection in the Month and Quarter slicer.

```Rank =
RANKX ( ALL ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Sales] ) ) )```

I’ve also uploaded my .pbix file here for reference.

Best Regards,

Herbert

Community Champion

@rajeev16 Easy way to do is to sort the visual ie if you're using column or bar chart click on three dots on top right hand corner and sort by field that you've used for Values section that will give you the top to bottom view and limit the size of the visual so that you're only seeing top 10 rows.

Complex way is to use RANKX function to get ranking of the values and then use visual-level filter to limit that to only show upto 10.

New Member

thanks ankit.

I am looking for the Rank command to filter the records of the top 10 customers.

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors