TOP N and "OTHERS" values

[Anonymous]

Hi good morning!

I have a problem creating a Dax Formula in PowerBI.

I have two fields, one of "Consume" and another one is a "Cost x Consume".

The problem is that there are many types of consume which leaves the graphic very polluted.

What I would like to do to show all types of consume is to use a TopN 5, 10 .. filter where the most expensive types of consume appear and in the end a "grouping of all other consumptions" in only one with the name of others.

An image to leave more detailed my doubt:

I'm sorry for my bad english.

Thank you!

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create the new type column and drag it to 'Legend'. For example: Show the top 3 values in Donut chart.

Rank =
RANKX (
    ALL ( Table2[type] ),
    CALCULATE ( SUM ( Table2[value] ), ALLEXCEPT ( Table2, Table2[type] ) )
)

NewType = IF(Table2[Rank]<=3,Table2[type],"Others")

Regards,

Cherie

[Anonymous]

@v-cherch-msft  i am using your solution and wanted to extend the funtionality by providing a TopN menu, like so 

and change 'New Type' like so

New Type = IF(Sheet1[Rank] <= max('TopN'[TopN]); Sheet1[type];"Others")

I dont get any errors but the donut chart just doesn't react to the selected value.  Thx in advance

[Anonymous]

Thank you very much! It worked!

[Anonymous]

Hi Pedro,

Perhaps this tutorial help you to achive your needs.... and it's in Spanish.

Tell us if works for you.

[Suetam016]

This is very elegant. 11:00 if anyone want's a timestamp, but the whole vid is gold