TopN+Others using Pie chart

[community_pinki]

Hi All,

I have a Pie chart where each sub-category contributing to the total sales.
Now, I have a requirement that the pie chart should show only top 5 sub-category and remaining should be in Others part.
How can we do that using DAX?

Any Help would be Appriciated...

Thanks.

[Anonymous]

 I am getting an error, saying SUM function cannot work with STRING. Please guide me. I am looking for the same soluction.

[MFelix]

What is the type of data you are using? Do you want to make a sum of a field? A count?

[Anonymous]

Hello all, 

Would you please explain, how you created Calculated column? My query is not working. 

[Anonymous · Accepted solution]

Hi @community_pinki 

I think you can build a calculated column by DAX to show new subcategorys with Top5 and Others.

My Sample:

Top5 Flag is a calculated column.

Top5Flag =
VAR _Rank =
    RANKX (
        'Table',
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[SubCategory] = EARLIER ( 'Table'[SubCategory] ) )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( _Rank <= 5, 'Table'[SubCategory], "Others" )

Result:

Best Regards,
Rico Zhou

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

[MFelix]

Hi @community_pinki 

Check this detailed explanation about how to create the dinamic others part then you can use in any chart.

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/