Forum Discussion

rajeev16's avatar
rajeev16
New Member
10 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 wou...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 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

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 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