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. 👍
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!
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!!!
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!
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...? :-)
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
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 ) )
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.
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
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?
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!
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
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!
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!
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?
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
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]
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?
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 ?
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.
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.
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.
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.
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.
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?
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.
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.
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
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.
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.
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.
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
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
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.
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 :)