Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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
v-diye-msft
Community Support
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:

3.PNG

 

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.

View solution in original post

3 REPLIES 3
tschris
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.

v-diye-msft
Community Support
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:

3.PNG

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.