DAX Fridays!
Vložit
- čas přidán 11. 07. 2019
- 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
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!
Thanks!
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!
Glad to hear!
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. 👍
Very very useful tutorial. Thanks you very much Ruth!!! I like a lot your channel because it is structured and easy to find tutorial.
Thanks for the feedback:)
/Ruth
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.
Yes, i mentioned that at the end if the video :)
Pick the solution, based on your scenario!
/Ruth
Great vid - so simple!! Been battling this for days, thank you!
Glad it helped!
/Ruth
Very smart answer. Very cool. I'm impressed and thankful as this is something I was trying to do for my company.
Very elegant and interesting solution! I loved it!!!
I love to see your videos, I can makes a lot of ideas from a simple solutions.
Thanks
My pleasure Alexis!!
Thank you Ruth.
More wisdom for you.
:) and Happy Friday!
/Ruth
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!!!
At your service ☺️
Thats brilliant! Such an easy solution!
Your video is always useful! Thank you so much
Thanks for the feedback:)
/Ruth
I'll definately use it in my projects! Tks, Ruth
Wonderful ! Gerhard is the mastermind of the solution, so if you have time, give him some feedback on his blog!
/Ruth
Clever. Thanks for sharing your solution 👍
My pleasure, glad it was useful!
/Ruth
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 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
Wow, super cool will definitely use👍thanks for the great videos.
My pleasure and Happy Friday :)
/Ruth
@@CurbalEN Happy weekend😊
This is soooooo awesome Ruth! Thank you very much!!!
Yey!!
Thanks, you made that very simple!
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
)
)
Thanks for sharing!
You're amazing! Thank you for all!
Thanks! 🎉
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?
That was great! Thanks!
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.
Amazing... thanks for this video !!!!
Very useful solution!
Quick and easy!! ;)
/Ruth
Ruth, how do you enable/view the measure results on the Data tab? (I can only see the columns)
Perfecto, muchas gracias :-)
Hi Ruth, Many thanks for your videos. Is it possible to do the same under Direct Query?
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.
Ya even for me
Vey very nice tips...
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 ?
Awesome, I am looking for this kind of solution!!!
Wonderful and happy Friday!!
/Ruth
@@CurbalEN Happy Friday!!!
Thank you sooo much!!!
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!
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.
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]
For that, you need to use Gerhard’s measure. Check it out!
/Ruth
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 works only as a calculated column, for a measure, use the blog post I mention on the video.
You are bloody amazing
You too!
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
Both tutorials are useful. They go in more depth that I do 😊
I got it now, thanks
Glad you sorted it out!
/Ruth
What is that sales measure? Is product name unique in that table?
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, 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
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
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?
Found solution for this scenario?
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.
You have to create a calculated column instead of a measure
/Ruth
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
Very nice....
Thanks!
/Ruth
Hi I have an error that says "A circular dependency was detected: Table[Column]. What this could be? (My value is a measure)
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.
@@ritusinghal8631 Thanks for the help. I'll try it.
I cant find "download" sample files , i trying to following links but ... where are they??
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
Hi @shahabhaidar , I am facing the same issue did you get any solution for this. If yes Please provide the solution.
Thanks in advance
On 3:28 you write [Sales]. Is it a calculated column you have? I can't see it anywhere...
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
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?
Nice video :)
I get a circular reference error while using this! Can you suggest what could be the issue ?
I've got the same issue. do you already have a solution for this?
Add the calculated column to the dimension/lookup table, not the fact/transaction table.
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.
Hi! Could you ask in the power bi community? Thanks!
/Ruth
@@CurbalEN ok mam .thank you once again for wonderful tutorials .it's help lot for me.
Both from and to date diff must year .It is possible in power bi
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?
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
@@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
How we can remove duplicate rank?
I have to take top 5 and remaining need to show in "Other" category. Is this possible?
Try this:
m.czcams.com/video/kRtmb8ftyQc/video.html
/Ruth
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
Gracias!!
Un placer!
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
Add the calculated column to the dimension/lookup table, not the fact/transaction table.
This method is not working for me, though I followed all the steps correctly, please help
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?
I think on the video description I showed a resource to do it as a measure.
Perfect
🎉🎉
/Ruth
How to create the same thing but using a measure, not a calculated column? Thanks a lot.
The blog post I mention does that, check it out./Ruth
can you please share the formula for [Sales] that used in Rankx
Can you make dynamic TopN based on 2 dimension Column
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.
when i tried this i am getting error like circular dependency detected
Did you ever figure this out?
@@panospa I'm also getting this message - do you have some thoughts around why this might be happening?
@@roman1241 Unfortunately, I had the same error and could not figure out how to resolve it.
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
@@sunrayminor4726 YOU ARE A GOD SUNRAY!
It is cool... but doesn't work well when you apply filters to other dimensions on the dash. Has anyone faced the same issue?
It should, but it depends on how you have your model set up...
/Ruth
Unfortunately it does not work for me because of this: A circular dependency was detected.
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 :)
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.
A circular dependency error occurred when I try this
I am getting a circular dependency, pls help
Add the calculated column to the dimension/lookup table, not the fact/transaction table.
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.
I have a few videos solely on rank, search on my channel and you will find them.
/Ruth
Sales in one table and products in one table with blank values ... How it's work🤔
thanks, i think #10: RANK/ TOPN with slicer was better
Curbal.. if u a moeslem, u knowledge will send u to good paradise
Hi, it’s not obvious what exactly you are doing at 5:20 and around it...
:(
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.
Ruth , o meu dá uma dependência circular foi detectada. O que pode ser?
VAR Rank = RANKX(ALL(Vendas);[Valor];;DESC)
RETURN
Rank
Pues no se, describe tu modelo y el mensaje de error en la comunidad de power bi para recibir ayuda!
/Ruth
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
What if the Dynamic Top N is a measure ? This is one sided solution
nice you're gorgeous ,this works fine
U made little bit complicated this function