DAX Fridays!

[@lawrenceogun389]

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. 👍

[@brendonbird8988]

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!

[@CurbalEN]

Thanks!

[@BrunoAraujo-po2lm]

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!!!

[@CurbalEN]

At your service ☺️

[@lorenzoyniguez7008]

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!

[@CurbalEN]

Glad to hear!

[@golamgous1854]

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

[@garimaparmar140]

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?

[@carloscorradinidemianevald6369]

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!

[@shahabhaidar]

Hi Ruth, This works really great ! but what if we had to apply other filters within the report? for example. I need to show the top 5 products with others within the selected category. I tried writing below DAX but unfortunately it didn't workout. NProducts = VAR _ProductRanking = CALCULATE ( RANKX ( Products, [Total Sale],, DESC ), ALLSELECTED ( Products[Catogary] ) ) RETURN IF ( _ProductRanking

[@gangadharbatti1966]

Hi @shahabhaidar , I am facing the same issue did you get any solution for this. If yes Please provide the solution. Thanks in advance

[@sillylife13]

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

[@AmySwayzee]

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

[@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

[@srikanthravula90]

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

[@vishaldaharwal6773]

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

[@jbgonzalez13]

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

[@CurbalEN]

Thanks for the feedback:) /Ruth

[@JGNiDK]

On 3:28 you write [Sales]. Is it a calculated column you have? I can't see it anywhere...

[@CurbalEN]

You can see it if you download the file :) I think I did a calc column but you can create a measure with SUMX. The dataset is northwind. /Ruth

[@Fury_Boom]

Hey, thanks for this! I have one issue, although this is a really old video so it may not get seen, worth a try! I keep getting this error 'Special flag is not allowed as an argument number 3 of function 'RANKX'.' And fully don't understand it! I have followed your example to the letter and still get the error, any ideas? Thanks!

[@vamsidhargandikota2221]

when i tried this i am getting error like circular dependency detected

[@panospa]

Did you ever figure this out?

[@roman1241]

@@panospa I'm also getting this message - do you have some thoughts around why this might be happening?

[@panospa]

@@roman1241 Unfortunately, I had the same error and could not figure out how to resolve it.

[@sunrayminor4726]

Vamsidhar Gandikota You need to add the calculated column in the dimension table like has been done in the video (Product table) and not in the fact or sales table

[@luanfetter8387]

@@sunrayminor4726 YOU ARE A GOD SUNRAY!

[@saiyash]

Hi, this is very helpful, however this is not working for me. Pasted below is my query, and the results do not group the top 5 Currencies & others. My transactional data is for each day, with multiple currency values for the same day, different company. Appreciate any help - TopCurrencies+Others by Value = Var RankCurrencybyValue = RANKX(ALLSELECTED(DailyBalance[Currency]), [Value_MRate],,DESC) Return IF(RankCurrencybyValue

[@GHRichardson]

Hi Ruth, Another great video and one that makes me think "I can use that right now!". One improvement, though. I think that when we use this in a table, we'd probably want to have the table ordered by rank - but with the 'Others' at the bottom. The only way that I can think of to do that is to have have a separate calculated column that does the same ranking, but instead of using the product name it uses the rank value itself (and, say, 1000000 for the 'Other' option). If you then set your calculated column to be ordered by this new calculated column in the Desktop UI - using the 'Sort by Column' toolbar button, I think that it will give us a better ordering. I wonder if there is a better/neater way...? :-)

[@CurbalEN]

Yes, or maybe add the name with the ordering , so concatenate tank and product Name and as you know the ranking you want ( you hardcode it in the if statement) it is no problem to hardcode it for “other”, like: “5. Other” /Ruth

[@daveportland471]

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 ) SELECTEDVALUE[ProductsRanking] RETURN SWITCH ( TRUE (), SELECTEDVALUE ( ProductsGroup[Group] ) = "TOP n", Topselection, SELECTEDVALUE ( ProductsGroup[Group] ) = "Others", Rest ) ) ) > 0 ) )

[@CurbalEN]

Thanks for sharing!

[@gauravarora6611]

Nice video :) I get a circular reference error while using this! Can you suggest what could be the issue ?

[@robvermeer5951]

I've got the same issue. do you already have a solution for this?

[@sunrayminor4726]

Add the calculated column to the dimension/lookup table, not the fact/transaction table.

[@M4gpi3girl]

I would like to apply this to my dashboard, however I do not work with sales products. I have a stacked bar chart showing the main type of group a charity supports, and so instead of 'sales' I just use the count of each group for my table. How can the method in this video be applied without a 'sales' category to use? Really struggling to find any resources for showing Top N and Others for a dynamic visual that isn't completely sales-focused. Any help or signposting to other forums would be hugely appreciated!

[@RomanFilimonov]

Hi Ruth, Many thanks for your videos. Is it possible to do the same under Direct Query?

[@aydeediaz2307]

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

[@MartinKuzmicz]

Hi, Great video, thanks. But I think there is one problem. This is creating a ranking for the whole table (data). What if you need to use a date filter and have sales from the last month, so the rank should be only based on last month. Will this works too in this case?

[@dnyanjal5471]

Found solution for this scenario?

[@LeticiaSmirelli]

This is soooooo awesome Ruth! Thank you very much!!!

[@CurbalEN]

Yey!!

[@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

[@maxzr]

Hi Curbal. Thanks for this tutorial. I've tested the formula in a table with three years of sales registered monthly and it rank everything on the table so, there is no way to have an specific year/month combination (selected with slicers) rank. How could I solve it? Thanks in advance.

[@ritusinghal8631]

Hi Max, that's a relevant ask you raised. You need to modify your [Sales] DAX - used by Ruth in her RANKX. You need to modify your sum(sales) to accommodate for year/month context: CALCULATE( SUM('FACT'[SALES]), ALLEXCEPT('FACT', 'FACT'[MONTH], 'FACT'[PRODUCT]) ) Disclaimer: You'll have to perform this on your fact table and not Product.

[@maxzr]

@@ritusinghal8631 Thanks for the help. I'll try it.

[@christopherforbes5195]

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?

[@CurbalEN]

I think on the video description I showed a resource to do it as a measure.

[@TheLeotLion]

The data table I am trying to use this on has multiple years worth of data... and unfortunately the above doesnt seem to be working. Is there a way to use the filters to get the TopN+Other to sum by year to get the TopN/Yr+Other ?

[@info_bus273]

A circular dependency error occurred when I try this

[@karnikkalani1516]

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

[@samakh61]

Great Ruth :), I had a look at Gerhard post either , good point in his way of implementing is that it can dynamically cater for different values for N in top N.

[@CurbalEN]

Yes, i mentioned that at the end if the video :) Pick the solution, based on your scenario! /Ruth

[@srikanthravula90]

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

[@sohinisengupta6251]

This is definitely very helpful but didn’t understand how it’s dynamic!! Users cannot change the value for ‘N’ in DAX query/calculated column. It should be done using parameter no?? Also, how to sorting works to show ‘Others’ on top or bottom always, irrespective of the product names?

[@divyajha762]

Can you make dynamic TopN based on 2 dimension Column

[@alexisroj]

I love to see your videos, I can makes a lot of ideas from a simple solutions. Thanks

[@CurbalEN]

My pleasure Alexis!!

[@panospa]

Ruth, how do you enable/view the measure results on the Data tab? (I can only see the columns)

[@mathewinmuscat]

What is that sales measure? Is product name unique in that table?

[@the_legends1989]

Hi Ruth, There is a long dax for this in Alberto Ferrari's book which is too long and difficult to understand. You nailed it

[@CurbalEN]

Both tutorials are useful. They go in more depth that I do 😊

[@jamiew3986]

Thanks, this is great! However, I got another question relating to TopN. I have a bar graph where the values are shown as % of Grand Total, when I filter to TopN, the distribution changed. Is there any way to retain the % display?

[@jorparts]

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

[@smpa0137]

This is an awesome video !!! Thanks for this. However, I have a question. I followed your method and it does what is supposed to. However, it breaks the data lineage. I am doing a TOPN+Others calculated column for a Business Unit table. I have another Location table where I have those Business Units with Latitude and Longitude. I want to filter the Location table in the visualization window with the newly created TOPN+Others column following your method. But that column can't filter the location table as the data lineage is broken. Any idea how to retain the data lineage with this method. Thank you in advance.

[@Styles5001]

How are you able to enter Products[ProductName] (a table) in your if statement? I keep trying this and it wont let me bc IF statements require scalar values.

[@CurbalEN]

You have to create a calculated column instead of a measure /Ruth

[@phanidivi3613]

hi mam , i have one requirement related to cascading drop down list in power bi.When i select to-date ,automatically the from-date selected with one year before.

[@CurbalEN]

Hi! Could you ask in the power bi community? Thanks! /Ruth

[@phanidivi3613]

@@CurbalEN ok mam .thank you once again for wonderful tutorials .it's help lot for me.

[@miahoooooooo]

How to create the same thing but using a measure, not a calculated column? Thanks a lot.

[@CurbalEN]

The blog post I mention does that, check it out./Ruth

[@adriannacook313]

Hi Ruth, thanks for another wonderful video! I love the way you make things look so much simpler. I was wondering if you would be able to show me how to do the rank in a 3-level hierarchy. For example: Leve 1: I need to find out top 3 of product category by sales. Level 2: inside the top 3 category, I need to find out top 1 of sub category by sales. Level 3: inside top 1 of subcategory, I need to find out top 1 city by sales. Note: Category and city belong to 1 table, and subcategory belongs to a different table. Thank you so much!

[@DrBarati]

Hi, it’s not obvious what exactly you are doing at 5:20 and around it...

[@CurbalEN]

:(

[@rivanowilliam6610]

Thank you for the video! This is really useful for me. Can I use the if function as a measure instead of creating a new column? Because in my case, I need to top rank the count of "ProductName" (let's say I have 10 rows named Cote de Blaye and 5 rows of Thuringer Rostbratwurst) in which there is no column stating about this formula.

[@CurbalEN]

This works only as a calculated column, for a measure, use the blog post I mention on the video.

[@JGNiDK]

As I understand, you’re offering the PBIX file, that you use here, for free in your community download? The one with calendar in, that you’re using in this video? But I’m being prompted with a login for your Synology?

[@CurbalEN]

Hi Jan, you don’t need to login on my synology to get my Dax Fridays files. Just click on the link and download. /Ruth

[@JGNiDK]

@@CurbalEN On iPad the reaction from the webpage was different. It tried to open Synology Drive. On my pc it's fine now. Thank you (again) Ruth

[@willianarboleya5651]

You're amazing! Thank you for all!

[@CurbalEN]

Thanks! 🎉

[@karnikkalani1516]

will be still right if I add a slicer by year or it won't as top ten would be calculated without the slicer in consideration.

[@EricsMaineLife]

Great explanation! I’m trying to do this for top states in my store list table but it seems to only work at the lowest level of granularity. It works for top 10 stores and all other but when I try states it’s listing all states.

[@joshic.h8299]

Ya even for me

[@habidata]

Really helpful, tutorial. But I met an error when I through this approach with my data, "A circular dependency was detected: Master[TopDomain+Others]." Could anyone know how to deal with it. TopDomain+Others = VAR RANKTransactionByDomain = RANKX(ALL(Master), [1.Trans_Agreed],, DESC) return IF(RANKTransactionByDomain

[@sunrayminor4726]

Add the calculated column to the dimension/lookup table, not the fact/transaction table.

[@Navarrous]

It is cool... but doesn't work well when you apply filters to other dimensions on the dash. Has anyone faced the same issue?

[@CurbalEN]

It should, but it depends on how you have your model set up... /Ruth

[@matty_taylor]

Thanks, you made that very simple!

[@ayobamiologun9184]

Thank you Ruth. More wisdom for you.

[@CurbalEN]

:) and Happy Friday! /Ruth

[@Angel-o5d2x]

You are bloody amazing

[@CurbalEN]

You too!

[@tejeshgour1919]

How we can remove duplicate rank? I have to take top 5 and remaining need to show in "Other" category. Is this possible?

[@CurbalEN]

Try this: m.kzbin.info/www/bejne/oYPXnpVum9msh8U /Ruth

[@YoniWol]

Great vid - so simple!! Been battling this for days, thank you!

[@CurbalEN]

Glad it helped! /Ruth

[@ReneLopez-vb6gb]

Hello Ruth, thank you very much for your videos, all very useful! This example of showing a Rank N and Others has been very useful to me, maybe you can help me with this, let's say I have identified a Product X that I want to show it in a similar way to the Other row, that is, how to show a Top N but that does not include this product X, and then show it in another row, it would be to show Top N + Product X + Others, thanks for your help.

[@feng3625]

Your video is always useful! Thank you so much

[@CurbalEN]

Thanks for the feedback:) /Ruth

[@stephenbrincat7124]

Wow, super cool will definitely use👍thanks for the great videos.

[@CurbalEN]

My pleasure and Happy Friday :) /Ruth

[@stephenbrincat7124]

@@CurbalEN Happy weekend😊

[@belizatube]

I'll definately use it in my projects! Tks, Ruth

[@CurbalEN]

Wonderful ! Gerhard is the mastermind of the solution, so if you have time, give him some feedback on his blog! /Ruth

[@frankgovers8420]

Great ruth, I would put a variabele for defining top 1, 3, 5 10 in an other table , like selected top and then use this one in combination with a slicer TopnSales = IF([Topnothers]

[@CurbalEN]

For that, you need to use Gerhard’s measure. Check it out! /Ruth

[@Alexandrezification]

That was great! Thanks!

[@stephantoenjes4528]

Thats brilliant! Such an easy solution!

[@Dimiliv1981]

Clever. Thanks for sharing your solution 👍

[@CurbalEN]

My pleasure, glad it was useful! /Ruth

[@roxana-stefanaapetrei6109]

Unfortunately it does not work for me because of this: A circular dependency was detected.

[@mathewinmuscat]

I am getting a circular dependency, pls help

[@sunrayminor4726]

Add the calculated column to the dimension/lookup table, not the fact/transaction table.

[@andreviana1603]

Amazing... thanks for this video !!!!

[@brahmakiran6474]

Sales in one table and products in one table with blank values ... How it's work🤔

[@janet7934]

Thank you sooo much!!!

[@phanidivi3613]

Both from and to date diff must year .It is possible in power bi

[@vida1719]

Very useful solution!

[@CurbalEN]

Quick and easy!! ;) /Ruth

[@mathew9665]

Firstly thank so much for this, however this solution - It's not fully dynamic; is there not a solution for a Top N + Other which is updated per filter/selection interaction - The ranking, and values needs to change based on user selections It's rather frustrating as the ability to set a Top N filter to a visual, must determine this some how (it just returns the Top N requested) the remaining would just be those not selected.

[@premsagarchatla6404]

Awesome, I am looking for this kind of solution!!!

[@CurbalEN]

Wonderful and happy Friday!! /Ruth

[@premsagarchatla6404]

@@CurbalEN Happy Friday!!!

[@nikiss8]

cool. however wish you spent a bit more time on Rankx. Not everyone is familiar with the database you are using. I spent quite a while trying to rank products in one table. seems to work once I created table with unique names.

[@CurbalEN]

I have a few videos solely on rank, search on my channel and you will find them. /Ruth

[@joaquimcosta952]

Vey very nice tips...

[@ricardocontreras]

Gracias!!

[@CurbalEN]

Un placer!

[@paradeen2730]

Just a warning about this solution. Yes it will categorize into Others and will enable drill through, but will do that only on one filtered scope of the fact table. Filtering other dimension will most likely change the TOPN and Other categories so the usage is very limited. Just making a note about this since I almost started to use it... Ruth: You are great though, but this video you might remove since it's so very likely that the usage will not show the correct result

[@espensrensen7305]

Perfecto, muchas gracias :-)

[@alibaroroh9355]

Curbal.. if u a moeslem, u knowledge will send u to good paradise

[@mathewinmuscat]

I got it now, thanks

[@CurbalEN]

Glad you sorted it out! /Ruth

[@mohammadrezaei4221]

thanks, i think #10: RANK/ TOPN with slicer was better

[@ArthurPopoviMovo]

it is not working, A single value for column 'PRODUCT' in table 'PRODUCTS' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[@edgarabgar4633]

Didn't work for me, sorry. Category TopN+Others = VAR RankCategoriesByComplaints = RANKX(ALL('Complaint Details - Category Details'[Categorie]), 'Complaint Details - Category Details'[RowCount],,DESC) RETURN IF(RankCategoriesByComplaints

[@joaquimcosta952]

Very nice....

[@CurbalEN]

Thanks! /Ruth

[@anshusaxena1263]

Perfect

[@CurbalEN]

🎉🎉 /Ruth

[@andremorabarreira6299]

Hello! Could you please do a video explaining how to create temp tables? I would like to create this table as a temporary table and then use the rank function - all inside one measure. this way the rank would vary depending on the filters applied in the report. I would really appreciate this help :)

[@bhuruu]

What if the Dynamic Top N is a measure ? This is one sided solution

[@vilmarsantos3419]

Ruth , o meu dá uma dependência circular foi detectada. O que pode ser? VAR Rank = RANKX(ALL(Vendas);[Valor];;DESC) RETURN Rank

[@CurbalEN]

Pues no se, describe tu modelo y el mensaje de error en la comunidad de power bi para recibir ayuda! /Ruth

[@draco1847]

U made little bit complicated this function

[@rafangel7516]

nice you're gorgeous ,this works fine