DAX Fridays!

Подписаться 135 тыс.

Просмотров 53 тыс.

50% 1

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
Link to RANX video: • DAX Fridays! #9: RANKX...
Gerhard post: blog.gbrueckl.at/2019/05/powe...
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
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:
curbal.com/product/sponsor-me
Many thanks in advance!Here you can download all the pbix files: curbal.com/donwload-center
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #dax #powerbi #mvp

Опубликовано:

11 июл 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 148

@lawrenceogun389 10 месяцев назад

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 2 года назад

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 2 года назад

Thanks!

@jbgonzalez13 5 лет назад

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 5 лет назад

Thanks for the feedback:) /Ruth

@lorenzoyniguez7008 3 года назад

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 3 года назад

Glad to hear!

@AmySwayzee 2 года назад

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

@jorparts 3 года назад

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

@YoniWol 4 года назад

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

@CurbalEN 4 года назад

Glad it helped! /Ruth

@samanehkhakbaz8815 5 лет назад

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 5 лет назад

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

@alexisroj 3 года назад

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

@CurbalEN 3 года назад

My pleasure Alexis!!

@ayobamiologun9184 5 лет назад

Thank you Ruth. More wisdom for you.

@CurbalEN 5 лет назад

:) and Happy Friday! /Ruth

@stephantoenjes4528 4 года назад

Thats brilliant! Such an easy solution!

@feng3625 5 лет назад

Your video is always useful! Thank you so much

@CurbalEN 5 лет назад

Thanks for the feedback:) /Ruth

@BrunoAraujo-po2lm 2 года назад

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 2 года назад

At your service ☺️

@Dimiliv1981 5 лет назад

Clever. Thanks for sharing your solution 👍

@CurbalEN 5 лет назад

My pleasure, glad it was useful! /Ruth

@matty_taylor 2 года назад

Thanks, you made that very simple!

@belizatube 5 лет назад

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

@CurbalEN 5 лет назад

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

@LeticiaSmirelli 2 года назад

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

@CurbalEN 2 года назад

Yey!!

@willianarboleya5651 2 года назад

You're amazing! Thank you for all!

@CurbalEN 2 года назад

Thanks! 🎉

@stephenbrincat7124 5 лет назад

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

@CurbalEN 5 лет назад

My pleasure and Happy Friday :) /Ruth

@stephenbrincat7124 5 лет назад

@@CurbalEN Happy weekend😊

@Alexandrezification 2 года назад

That was great! Thanks!

@andreviana1603 11 месяцев назад

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

@jamiew3986 2 года назад

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?

@panospa 4 года назад

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

@janet7934 20 дней назад

Thank you sooo much!!!

@daveportland471 3 года назад

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 3 года назад

Thanks for sharing!

@smpa0137 4 года назад

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.

@GHRichardson 5 лет назад

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 5 лет назад

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

@adriannacook313 3 года назад

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!

@espensrensen7305 3 года назад

Perfecto, muchas gracias :-)

@vida1719 5 лет назад

Very useful solution!

@CurbalEN 5 лет назад

Quick and easy!! ;) /Ruth

@joaquimcosta952 3 года назад

Vey very nice tips...

@user-qt9cz2kp6w 9 месяцев назад

You are bloody amazing

@CurbalEN 9 месяцев назад

You too!

@TheLeotLion 2 года назад

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 ?

@RomanFilimonov 3 года назад

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

@Fury_Boom 2 года назад

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!

@ricardocontreras 2 года назад

Gracias!!

@CurbalEN 2 года назад

Un placer!

@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

@aydeediaz2307 Год назад

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

@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

@sillylife13 15 дней назад

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

@premsagarchatla6404 5 лет назад

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

@CurbalEN 5 лет назад

Wonderful and happy Friday!! /Ruth

@premsagarchatla6404 5 лет назад

@@CurbalEN Happy Friday!!!

@mathewinmuscat 4 года назад

I got it now, thanks

@CurbalEN 4 года назад

Glad you sorted it out! /Ruth

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

@mathewinmuscat 4 года назад

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

@joaquimcosta952 5 лет назад

Very nice....

@CurbalEN 5 лет назад

Thanks! /Ruth

@MartinKuzmicz 4 года назад

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 6 месяцев назад

Found solution for this scenario?

@rivanowilliam6610 3 года назад

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 3 года назад

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

@EricsMaineLife 4 года назад

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 3 года назад

Ya even for me

@maxzr 4 года назад

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 4 года назад

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 4 года назад

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

@Styles5001 4 года назад

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 4 года назад

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

@karnikkalani1516 10 месяцев назад

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

@ReneLopez-vb6gb 3 года назад

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.

@saiyash 3 года назад

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

@shahabhaidar 4 года назад

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

@JGNiDK 5 лет назад

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

@CurbalEN 5 лет назад

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

@phanidivi3613 5 лет назад

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 5 лет назад

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

@phanidivi3613 5 лет назад

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

@frankgovers8420 5 лет назад

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 5 лет назад

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

@anshusaxena1263 4 года назад

Perfect

@CurbalEN 4 года назад

🎉🎉 /Ruth

@JGNiDK 5 лет назад

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 5 лет назад

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 5 лет назад

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

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

@gauravarora6611 4 года назад

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

@robvermeer5951 3 года назад

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

@sunrayminor4726 3 года назад

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

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

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

@tejeshgour1919 5 лет назад

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

@CurbalEN 5 лет назад

Try this: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kRtmb8ftyQc.html /Ruth

@srikanthravula90 Год назад

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

@srikanthravula90 Год назад

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

@habidata 3 года назад

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 3 года назад

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

@miahoooooooo 4 года назад

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

@CurbalEN 4 года назад

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

@divyajha762 3 года назад

Can you make dynamic TopN based on 2 dimension Column

@Navarrous 4 года назад

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 4 года назад

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

@karnikkalani1516 10 месяцев назад

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.

@phanidivi3613 5 лет назад

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

@vamsidhargandikota2221 4 года назад

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

@panospa 4 года назад

Did you ever figure this out?

@roman1241 4 года назад

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

@panospa 4 года назад

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

@sunrayminor4726 3 года назад

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 3 года назад

@@sunrayminor4726 YOU ARE A GOD SUNRAY!

@info_bus273 Год назад

A circular dependency error occurred when I try this

@mathewinmuscat 4 года назад

I am getting a circular dependency, pls help

@sunrayminor4726 3 года назад

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

@roxana-stefanaapetrei6109 5 месяцев назад

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

@mathew9665 3 года назад

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.

@brahmakiran6474 2 года назад

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

@andremorabarreira6299 3 года назад

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 :)

@nikiss8 4 года назад

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 4 года назад

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

@alibaroroh9355 Год назад

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

@mohammadrezaei4221 3 года назад

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

@paradeen2730 27 дней назад

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

@DrBarati 2 года назад

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

@CurbalEN 2 года назад

@user-ky9hv2xd5t 6 месяцев назад

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.