cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
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
Highlighted
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.

Highlighted
New Member

thanks ankit.

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

Highlighted
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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors