How to create Power BI TOP N and others Report

[gauravnarchal]

Hello All.

I need to get the top 2 client group sales and the rest is to be shown as others. TopN client (number [No.]) will be selected from the filters (Advance filter) and rest all clients should show as other.

What best measure should I use to this result?

[Anonymous]

ok hear you go got the link to work PBIX

i hope this is a good solution for you 

[v-yingjl · Accepted solution]

Hi @gauravnarchal ,

1. Create a new table manually like this:

2. Create these measures:

Total Sales =
CALCULATE (
    SUM ( 'Table'[Sale Amt] ),
    ALLEXCEPT ( 'Table', 'Table'[Client Group] )
)

Sales Amount Top =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] ) )
)

Sales Amount Other =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        EXCEPT (
            ALL ( 'Table'[Client Group] ),
            TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] )
        )
    )
)

Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( 'Table'[Client Group] ) ), [Sales Amount Other] )

Sales Amount Top & Others =
IF (
    HASONEVALUE ( 'Top & Other'[Top] ),
    SWITCH (
        VALUES ( 'Top & Other'[Top] ),
        "Top2", [Sales Amount Top],
        "Other", [Sales Amount Other Total Only]
    ),
    [Sales Amount Top]
)

3. Use a Matrix visual to show the result:

Attached my sample file that hopes to help you: How to create Power BI TOP N and others Report.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I would create a calculated table based on your distinct client groups. 

then add the following columns ot the table :
-Sales = Sum(sales amnt)

-rank = RANKX(All(client groups), [Sales],,,Dense)
-topn = if([rank] <= 2 , "Top 2", "Other" )

Does this make sense? 

[gauravnarchal]

@Anonymous  It is not summarizing others in one row. See below screenshot.

[Anonymous]

can you e-mail me a copy of the PBIX file and i can see what i can do to fix it 🙂

[gauravnarchal]

@Anonymous Here is the PBIX file

[Anonymous]

this is the end result of the matrix 

send me your e-mail and i can send your way sorry one drive is giving me issues tonight not enough coffee for it 🙂

[Anonymous]

can you send me your e-mail it wont attach the link for some reason 

it is all fixed and is a good solution