Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.