cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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.

2 ACCEPTED SOLUTIONS
Super User
Community Support

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:

https://community.powerbi.com/t5/Desktop/How-to-order-by-column-name-and-measure-value/m-p/860531#M412845

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:

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
3 REPLIES 3
Regular Visitor

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.

Community Support

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:

https://community.powerbi.com/t5/Desktop/How-to-order-by-column-name-and-measure-value/m-p/860531#M412845

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:

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors