cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.

top N customer based on filter_1.jpg

 

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] ) ) )

top N customer based on filter_2.jpg

 

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

 

Best Regards,

Herbert

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
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

thanks ankit.

 

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

Highlighted
Microsoft
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.

top N customer based on filter_1.jpg

 

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] ) ) )

top N customer based on filter_2.jpg

 

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

 

Best Regards,

Herbert

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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