DAX Fridays! #132: Dynamic TopN + Others in Power BI

I was recently asked to do a TopN + Others for a customer and in this video I will share my solution with you. #curbal #dax #daxfridays #powerbi

SUBSCRIBE to learn more about Power and Excel BI!

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#daxfridays #curbal #dax #powerbi #mvp

[lawrenceogun]

This is a great solution you've just provided here. Thank you very much. I've wondered around the web just to find a simple fix and you've provided it. 👍

[brendonbird]

I know this is an older video, but thank you. Helped me solve a problem I have been sitting with. Really enjoy the way you explain the logic! Thank you!

[BrunoAraujo-polm]

Omg... You know how I've been struggling to do this on my company whitout sucess!!???? You, ve just saved my life and my sanity! Hahaha Also you are the best Power BI youtuber I've found so far, THANK YOU very much!!!

[lorenzoyniguez]

This is great! I've been looking for a good way to add this and definitely easier to follow and implement than some of the measure solutions I have found. Thank you!

[garimaparmar]

If I apply any filter to this, I want to see the top 5 according to the filter and the rest values should fall into others according to the filter selected.
How can I achieve this?

[golamgous]

Thank you for this video, I was looking for some sort of solution like this, but bit of different way. I got top sales client with business source, I need to display top 5 +others for each business source. Some how I was able to make it but the grand total of the all rows is coming wrong. Could you please help me on that

[jbgonzalez]

Very very useful tutorial. Thanks you very much Ruth!!! I like a lot your channel because it is structured and easy to find tutorial.

[vishaldaharwal]

Ma'am how to use parameters in place 4 ????

[AmySwayzee]

Very smart answer. Very cool. I'm impressed and thankful as this is something I was trying to do for my company.

[aydeediaz]

Hi I have an error that says "A circular dependency was detected: Table[Column]. What this could be? (My value is a measure)

[karnikkalani]

its not working for me does the Products name column need to be unique? I have issues with ranking as i have multiple entries of the same products

[christopherforbes]

This works well when you can create that calculated column, but how can we achieve this if we are basing the rank on a dynamic measure?

[sillylife]

I cant find "download" sample files, i trying to following links but ... where are they??

[carloscorradinidemianevald]

Looks good but it might not work when you are using quarterly sales information since it will pick up as top N the ones with the highest sales records counting from the beginning of the dataset, not the top N corresponding to the most actual date. Any tips on that?
Thanks!

[udayshankarnellore]

Am using this TopN+Others in Column Chart but I want to show Last Six months data dynamically when I change Month Year slicer. I tried but this TopN+Others is not working.
Please help with solution for this

[srikanthravula]

can you please share the formula for [Sales] that used in Rankx

[welcometomathy]

Can I do instead of Others and Top N can I make a row for Avg, Median, Total, like add a metric name to the metrics table

[srikanthravula]

This method is not working for me, though I followed all the steps correctly, please help

[jorparts]

Very elegant and interesting solution! I loved it!!!

[daveportland]

Great video as usual . For a dynamic solution I would like to share this the following . It only needs 2 disconnected tables : one named 'ProductsGroup with 1 column [Group] having 2 rows " Top n" and "Others" .The second table named 'ProductsRanking' is a simple one column table [ProductsRanking] for TOP n selection ( 1, 2, 3, 4 etc..) then the measure is quite easy using the dynamic segmentation technique and rankx function where the "TOP" is a rankx at or below the selected value and the "Rest" is a rankx above the selected value as follows:
Top N and Others=

CALCULATE (
[Sales amount],
FILTER (
VALUES ( sales[Products] ),
COUNTROWS (
FILTER (
'ProductsGroup',
VAR Topselection =
RANKX ( ALL ( sales[Products] ), [Sales amount], , DESC ) <=
VAR Rest =
RANKX ( ALL ( sales[Products] ), [Sales amount], , DESC ) >
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( ProductsGroup[Group] ) = "TOP n", Topselection,
SELECTEDVALUE ( ProductsGroup[Group] ) = "Others", Rest
)
)
) > 0
)
)