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.
I would like to create a table which shows my most important customers first and than ranks it based on margin.
The table must ranks from the highest to the lowest margin, However due to certain reasons some clients with lower margin must be shown first. The number of clients can very. By using the word "TOP20" in the name of these customer I can filter on "contains""TOP20"
However in the table it only shows the names, which contains TOP20
I would like to make a table that first shows the customers which contains "TOP20" (for example 3 names) and than fill the rest of the table based on the highest margin.
It will look like this
TOP20 CustomerB - $10000
TOP20 CustomerE - $5000
TOP20 CustomerG - $7000
CustomerA - $100000
CustomerC - $70000
CustomerD - $50000
CustomerF - $20000
CustomerH - $20000
etc...
Does anyone know how to do this?
Solved! Go to Solution.
@Anonymous,
You may add calculated columns as shown below.
groupId = IF ( SEARCH ( "TOP20", Table1[customer],, 0 ) = 0, 2, 1 )
rank = RANKX ( Table1, Table1[groupId],, ASC, SKIP ) + RANKX ( FILTER ( Table1, Table1[groupId] = EARLIER ( Table1[groupId] ) ), Table1[margin], , DESC, SKIP ) - 1
@Anonymous,
You may add calculated columns as shown below.
groupId = IF ( SEARCH ( "TOP20", Table1[customer],, 0 ) = 0, 2, 1 )
rank = RANKX ( Table1, Table1[groupId],, ASC, SKIP ) + RANKX ( FILTER ( Table1, Table1[groupId] = EARLIER ( Table1[groupId] ) ), Table1[margin], , DESC, SKIP ) - 1
Thank you @v-chuncz-msft for your reply.
I did what you said and made 2 extra colums
Now I assume that I have to sort the customer column by selecting the customer column en click "sort by Column""rank".
But sorting this column won't work when I try this. Am I doing something wrong?
Found it. I just have tot klik on the column header of rank to do this
Thank you very much for your help
I would like to create a table which shows my most important customers first and than ranks it based on margin.
The table must ranks from the highest to the lowest margin, However due to certain reasons some clients with lower margin must be shown first. The number of clients can very. By using the word "TOP20" in the name of these customer I can filter on "contains""TOP20"
However in the table it only shows the names, which contains TOP20
I would like to make a table that first shows the customers which contains "TOP20" (for example 3 names) and than fill the rest of the table based on the highest margin.
It will look like this
TOP20 CustomerB - $10000
TOP20 CustomerE - $5000
TOP20 CustomerG - $7000
CustomerA - $100000
CustomerC - $70000
CustomerD - $50000
CustomerF - $20000
CustomerH - $20000
etc...
Does anyone know how to do this?
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |