Forum Discussion

rajeev16's avatar
rajeev16
New Member
9 years ago
Solved

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

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      rajeev16
      New Member
      9 years ago

      thanks ankit.

       

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

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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