How to create “others” in a pie chart

[akhaliq7]

I need a way to show for example top 3 categories for sales and the rest of the categories in a others value. E.g. sales[orders] and categories[category name] are the tables and fields being used.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Orders TopN and Others =
VAR topnorders =
    CALCULATE (
        SUM ( Sales[Orders] ),
        KEEPFILTERS (
            TOPN (
                SELECTEDVALUE ( 'TopN'[TopN] ),
                ALL ( Categories ),
                CALCULATE ( SUM ( Sales[Orders] ) ), DESC
            )
        )
    )
VAR allorders =
    CALCULATE ( SUM ( Sales[Orders] ), REMOVEFILTERS () )
RETURN
    IF (
        SELECTEDVALUE ( Categories[Category] ) = "Others",
        allorders - topnorders,
        topnorders
    )

[akhaliq7]

I tested this solution but the others part always shows the sum of all the categories. When the others value should change so if there are 3 categories shown the others should show only show the sum of values for the remaining 16 categories.

[mykytaf]

Never too late - I looked into this issue and managed to correct the code for the measure used. 

Orders TopN and Others = 
VAR topnordersTable =
    TOPN (
        SELECTEDVALUE ( 'TopN'[TopN] ),
        ALL ( Categories ),
        CALCULATE ( SUM ( Sales[Orders] ) ), DESC
    )
VAR topnorders =
    CALCULATE (
        SUM ( Sales[Orders] ),
        KEEPFILTERS ( topnordersTable )
    )
VAR otherorders =
    CALCULATE (
        SUM ( Sales[Orders] ),
        REMOVEFILTERS ( Categories ),
        EXCEPT ( ALL ( Categories ), topnordersTable )
    )
VAR allorders =
    CALCULATE ( SUM ( Sales[Orders] ), REMOVEFILTERS () )
RETURN
    IF (
        SELECTEDVALUE ( Categories[Category] ) = "Others",
        otherorders,
        topnorders
    )

 What this does is it makes sure topnorders value is not set to 0 in the return statement, (which was an issue in the original code) as 'Other' is not a top N value, which cause for this variable to be set to 0 at the very end of the code.

[PiEye · Accepted solution]

Hi Akhaliq!

The best way I've found to do this so far is to create a DAX table and measure that work this out for you on the fly. Not my own solution, but it's worked for me and has been very useful.

Gerhard Brueckl provides a great explanation in his blog: https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

Basically you create the dax table with all the categories unioned with an "other" row, and within that use a measure that returns a value only for the topN categories, and the remaining total in the "other" row.

Let us know if this helps!

Pi