Sort Top 10 & Others table

[Anonymous]

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.

[amitchandak · Accepted 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

[tschris]

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 · Accepted solution]

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:

[amitchandak · Accepted 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