Forum Discussion
Sort Top 10 & Others table
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.
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/367415Hi 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:
3 Replies
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/367415Hi 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:
