Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |