top N customer based on filter

[rajeev16]

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

[v-haibl-msft · Accepted solution]

@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

[ankitpatira]

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

[rajeev16]

thanks ankit.

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