How to drive the Top/Bottom 10 Customers.

[Anonymous]

Hi Team,

We have customers more than 100 and we would like to take the Top 10 customers and bottom 10 customers based on the contribution figures.

Can anyone please help with formulas/measures to drive the desirable results.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For your requirement, you could try this way as below:

Step1:

Create two rank measure for [Contribution] measure

Rankx asc= RANKX(ALL('Table'[Customer Short]),[Contribution],,ASC)

Rankx desc = RANKX(ALL('Table'[Customer Short]),[Contribution],,DESC)

Step2:

Create a conditional measure that shows Top/Bottom 10 Customers.

In my simple sample, I just show Top/Bottom 2 Customers

Top/bottom 2 Customers = IF([Rankx asc]<=2||[Rankx desc]<=2,[Contribution])

Result:

and here is my sample pbix file, please try it.

Regards,

Lin

[Tahreem24]

Hi,

You can select you table visual then go to visual level filter and select the customer name-->advanced filter - - >top n--> drag the column name on which you want to rank your customer

Please give Kudos this effort and mark it as a solution if it helps.

[AlB]

Hi @Anonymous 

If this is a table visual, you can use the TopN/BottomN filter on the Contribution column

Ir it is an actual table, you can create another table with

TOPN( 10, Table1, Table1[Contribution], DESC )    // Top10

TOPN( 10, Table1, Table1[Contribution], ASC )    // Bottom10

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Hi AlB,

Do I need to create the another table or can create measure or Column with given formula in exiting table.

I tried to create the column but thorowing error for the given measures, Can you please guide here

Thanks,

Maruti Kunjatwad

[AlB]

Like I said, you can create new tables or you can do it in table visuals. For creating tables, go to the "Modelling" tab and "New table" and enter the code as follows (Table1 is your original table, the one you show):

NewTableTop10 = TOPN( 10, Table1, Table1[Contribution], DESC )

then another one for the bottom 10:

NewTableBottom10 = TOPN( 10, Table1, Table1[Contribution], ASC )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers