Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello everyone,
I have created a table where I have the Top 10 customers by AR amount and all the rest customers' amounts are aggregated in Others.
I want to sort by AR amount descending and have always the Others at the bottom of the table.
Now when I sort by AR amount I get:
Customer AR
Others 12,223,445
Customer
Amazon 3,415,788
Costco 1,265,678
Staples 674,789
Walmart 354,897
... ...
... ...
Total ...
Total ...
What I want to have is like below:
Customer AR
Customer
Amazon 3,415,788
Costco 1,265,678
Staples 674,789
Walmart 354,897
... ...
... ...
Total ...
Others 12,223,445
Total ...
Could you please advise me on how I can make this type of sorting?
I would like to mention that the AR amount is a measure. that calculates the AR for Top 10, others and total.
For the Customers there is a table like that:
Amazon Customer
Costco Customer
Staples Customer
Walmart Customer
... Customer
Others Others
Thank you all.
Regards.
Solved! Go to Solution.
@Anonymous , not very clear, see if this can help
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
top 5 with other
https://community.powerbi.com/t5/Desktop/Top-5-and-others/td-p/165945
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Hi @Anonymous
Seems like you'd wanna get the TOPN based on different category : Customer/Others, and show it in the matrix/table. but the category should be ranked as you wish by Custmer>Others, right?
You might refer to rank by multiple columns:
I modified the measure as:
Measure 4 =
VAR t =
SUMMARIZE (
ALLSELECTED ( 'Table (4)' ),
'Table (4)'[Code Group],
'Table (4)'[Name],
'Table (4)'[Rank],'Table (4)'[Sales]
)
RETURN
COUNTROWS (
FILTER (
t,
ISONORAFTER (
'Table (4)'[Code Group], SELECTEDVALUE ( 'Table (4)'[Code Group]), ASC,
'Table (4)'[Sales],SELECTEDVALUE('Table (4)'[Sales]),ASC
)
)
)
That the code group ranks by asc, and sales ranks by desc:
I am adding as an alternative solution this one. I have added the respective files in github if one would need to download an existing example.
Hi @Anonymous
Seems like you'd wanna get the TOPN based on different category : Customer/Others, and show it in the matrix/table. but the category should be ranked as you wish by Custmer>Others, right?
You might refer to rank by multiple columns:
I modified the measure as:
Measure 4 =
VAR t =
SUMMARIZE (
ALLSELECTED ( 'Table (4)' ),
'Table (4)'[Code Group],
'Table (4)'[Name],
'Table (4)'[Rank],'Table (4)'[Sales]
)
RETURN
COUNTROWS (
FILTER (
t,
ISONORAFTER (
'Table (4)'[Code Group], SELECTEDVALUE ( 'Table (4)'[Code Group]), ASC,
'Table (4)'[Sales],SELECTEDVALUE('Table (4)'[Sales]),ASC
)
)
)
That the code group ranks by asc, and sales ranks by desc:
@Anonymous , not very clear, see if this can help
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
top 5 with other
https://community.powerbi.com/t5/Desktop/Top-5-and-others/td-p/165945
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
67 | |
65 | |
42 | |
42 |
User | Count |
---|---|
46 | |
38 | |
28 | |
26 | |
26 |