Since I wasn't sure about downloading the source file, I generated dummy data to do the tutorial with, using this query in SQL Server: with CTE as ( select 1 as ID union ALL select ID +1 from cte where id
Mmmmm that is beyond my comprehension but creative people is the best I know!! :) Happy holidays and thanks for all your contributions here! Really appreciate it!
By the way, thanks goes to Mister James for supplying a good random function: theycallmemrjames.blogspot.com/2009/04/truly-random-number-in-ms-sql.html
Hello Ruth, Thanks for all you do here, I am actually learning the underlying of DAX through the DAX friday and I actually used this TopN at work yesterday, it kinda felt like a walk in the park. Thanks again.
Ruth , I love all you videos :) my current dying need is that I want to have top N written on a drill through page. So the dill thru is already passing my selected filters, but now I need to apply top N on this drill thru visual and get the right numbers on one category , and importantly to export right rows in Excel. Thanks Look forward
Hi Ruth,Once again a nice video. Will u be able to give us such a nice video about the EARLIER function as well?? I found out, that you can do the looping events using the earlier function along with the ALL function,but im not very much aware about it. Thank you very much Ruth. Keep it up. Cheers!!!!!
Hi Ruth, a nice movie. Need help - if my measure is SUM (instead of X), then the total with the selection Top 3, / Top 5, etc. do not give us the correct value (it gives us the max Total). How to solve to get the correct value. Thanks.
Hello! It is a very good video! I like a lot! But I'm a little bit confuse with the use of filters. When can I use filter by default just by draging it to the cava, and when I should creat the filter manually? I've been draging all the filters and now I don't know when it is appropiate to create them manually.. I understand if I want to show rankings I should create the filter as you explain, but in which other option I should do it? Regards!
I noticed that you popped into DAX studio to evaluate your expression. I would love to learn more about that as I have been struggling with an expression and was wishing I had a tool to step through my code. Do you have or would you be willing to create a walkthrough of DAX studio for this purpose?
Hi Curbal. Nice vid. Instead of using MAXX to return something in the event that the TOPN slicer isn't selected, I think it might be more efficient leave it with BLANK(), and instead change your approach so thta the AvgMeasurementValue only gets triggered if the filter is applied, and otherwise defaults to just the unadulterated [AvgValue] measure. So use this pattern =IF(HASONEVALUE(TopTable[TopN]) , [Existing AvgMeasurementValue pattern], [AvgValue])
Thank you for such a great video ! I have 1 question: What if I want to display the correct total for top 3, top 5, top 10 when selected in the slicer ?
I enjoy working through these videos. I think it looks better if you filter the entire table by the TopN slicer rather than just putting blanks in the column. You can do this by using a measure something like this: TopNXX = var SelectedN= if(HASONEVALUE(TopNTable[TopNValues]),SELECTEDVALUE(TopNTable[TopNValues]),max(TopNTable[TopNValues])) return switch(true(),SelectedN=0,1, [Rank]
Hi, when u select topn 3/5, the avg of machine measure is not appearing. Secondly rank total appearing is 6 everytime which is not correct. Pls let us know if there is second video of this rankx
Buenos días, una consulta estoy viendo en la última parte que editas la función: SelectedTopNValue, sin embargo no veo que esa medida se use, solo veo que pusiste la medida: AvgMeasurementValue como columna, podrías explicarme ello. Gracias
Interesting video. For some reason the new measures only work on mine if I use the built in format 'TopN' and not without single quotation marks as yours was. Not sure if this is a version quirk?!
Hi Ruth, great example. Thanks. But, what about the totals? when you select top 5 or top 10 the total should be filtered, right? how do you do that? many thanks :)
Hi Ruth, Thanks so much for your well-explained video! I would like to ask if there is a way to communicate to all other columns in the table while applying the TopN slicer? Eg if TopN10 is chosen, the table will only show first 10 rows across all columns. Kindly assist. Thank you! :)
Oh. What I meant is, for example at 8:55 of this video, when slicer TopN5 is selected, only the 2nd column shows up 5 values but the 1st and 3rd columns remain with 10 values each. Is there a way to work dynamically for all 3 columns?
Hi Ruth, In the filter at the beginning of the video there was an option of "All". How does one achieve an "All" option in addition to the Top 3, 5, and 10? Thanks!
+Patrick Sullivan Hi! It is a setting in the filter itself. Select the filter, go to the formatting pane and the first or the second setting you will be able to turn that on. Sorry for the vague instructions, I am pulling from memory. /Ruth
Hi, If you look at video, in initial video description, you can see Full Total is shown 10.56, irrespective of what you select in TOP N filter, however later in Video Total is coming based on your selection or even sometimes its not coming. Can you please let me know, how to get full total even though any TOP filter is selected. Regards Digant
Hi Ruth, Excellent video, many thanks for this. One question, what is we don't want to do Avg Value and rather use the Value column from the Table to display top 5 or top 3 Value.
+Devang Mistry I will have to re-watch the video, But until I do, there are always multiple ways to solve the same thing, so you are probably right! /Ruth
How can you use this for filtering on basis a measure that is not on the view. for example for the top N inventory items based on value, how can i filter to see the sales or order on hand. so i will have the sales data or order data in the view but the list to be filtered for the top N items that i am holding in stock.
Is there a way I can export my topN table with slicer? Whenever I export to excel, it exports the whole rows (sample: 50 rows instead of the top 10) Thank you!!!
Hola Ruth, Pequeña sugerencia amistosa de la India, Cada vez que explique algo nuevo, puede tomar datos que todos puedan entender FÁCILMENTE sin perder demasiado tiempo en eso ... en otras palabras ... puede usar algunos datos de ventas o cualquier información que pueda comprenderse fácilmente. Las personas no deben tomar 5 minutos para entender deteniendo el video una y otra vez solo para comprender los datos de muestra. De lo contrario, tus videos son increíbles. La razón por la que estoy diciendo esto es ... está vaporizando mis niveles de entusiasmo. Raani Chennamma De la India
Hi i have small doubt while we are displaying Rank value usine rankX DAX function.Some times we are getting same rank for same values right. For ex: Machine 11 ---- 0.65 --- 8 Machine 14 ---- 0.65 --- 8 Here, Could you please tell me .I want to display the unique rank based on machine numbers.ofcourse values are same but machines are diffrent right.How could we show the different rank? in this point of scenarios
Very interesting video - Thank you! Unfortunately I did not find here the answer to my problem. Could you point me to your video where you explain how to highlight one record in the table which is the ranking of e.g. cities depending on the chosen name in the slicer using DAX?
Hi Curbal your videos are amazing. could you please suggest me something on my current query. where i want to show past ,present and future data based on month selection ,if user selects Mar-19 ,he should see 3 visual one for past one for present one for future with two columns ,product name and its delivery date. kindly suggest.
Que tal Ruth? Excelente video, me sirvió de mucho para este tipo de reportes. Solo que tuve un percance y aún no entiendo porque no funciona. Cuando uso RanX para los items con la tabla "ALLSELECTED('TABLE')", el filtro funciona bien seleccionando cualquier opción, excepto cuando coloco "Seleccionar Todo", en ese momento la medida solo me muestra del rank Nro 1, y obvia el resto. Ya revise el medida que contiene el numero de seleccion del valor del TOPN, y obtiene el valor adecuado, pero la medida del valor a mostrar en la tabla solo me muestra de 1 solo item. Cuando cambio esta opcion en la medida del Rankind, de "AllSelected" a "All". El problema se soluciona. Me gustaría entender por qué tiene ese comportamiento con "AllSelected" con el fin de entenderlo mejor. Me puede ayudar? Slds cordiales
Hi Curbal, Thank you for this video. I had a question - Suppose the user selects top 3 values is there a way to group(sum/avg) the remaining values of the remaining machines. For example if we are looking at sales of 12 different machines in a pie chart and you just want to see/focus on the top 3 but also want to see the sales of the remaining 9 machines grouped into another category 'Other' and show it on the pie chart as a 4th slice of the pie, is there a way to do that?
Roth, will do so. Have already posted this as an idea as Power BI should add the Top N as a Quick Measure and support the Other categories like ZoomCharts provides. Would appreciate if you and others could support the idea (ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20317618-show-hide-top-x-categories-in-visuals-with-option )
In addition to that I found this post (community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672) which when combined with Top N table/measure logic you explained would address my requirement. Would have liked this to be out of the box for Power BI or at least as Quick Measure
The 'Average Value' field seems to be an average value per machine, i.e. some group by machine calculation was done. Did the Average value = medianx(data, data[values]) statement do the grouping? I am referring to 1:31 of the video above.
Quick question - how you can you get top X N and then the remaining as Others. Ideally we would be able to click into other to then show the next top X n (with any remaining as others ) and so on. is this doable
I have seen it done, but never tried it myself. Do a quick google search for “topn others power bi” and you will get some suggestions on how to do it. Happy Friday!! /Ruth
I found this video extremely helpful for something I'm working on. You mention that it is possible to exclude the non displayed values from the matrix totals. I was wondering if you have posted a video on how to do this or can describe how to?
thank you for great video series. I had a question and I would appreciate you could provide answer to it. Is there a way to display only the top n records and not all the records after you apply the filter?
hi curbal: thank you for the message. I am referring to after we select top 10 on the slicer thr logic filters top 10 for the average measure column but still shows all the machines and the ranks listed. Is there a way to filter the machines and ranks along with the average measure. hope I am able to explain.
Hi Ndb, I am afraid I still dont understand your question. When you filter by, for example top 3, the list will show you the top3 measurement values and their corresponding machines. What do you mean when you say that it shows all machines? /Ruth
Hi, Ruth, thanks for your tutorials I´m learning a lot with it! Does this file is still avaiable for download? I couldn´t find it on Community downloads =(
Hi Ruth, I followed you step by step to do TopN like you did, but, was not success. I replaced [Avg Value] by [AvgMeaurementValue] but noticed that can not update the visual coz have somethings wrong in [AvgMeasurementValue], (I checked again and agian to compare with you formula), consider using VALUE or FORMAT Function to fix it. Have you ever got this error like this? and how to fix it?
Hi Ruth, I learned then tried to apply to my BI Report. I'll do it again then record a clip then send you for more detail. Thank you for very usefull Topic "DAX Friday". /Viet
when I select top 3,5,10 I get total of table at bottom , in your case u don't get it , if I need total as well for top 3,5,10 how to get it in table .. thanks
This works for a table but I am trying to get it to work for a stacked column chart and it does not filter to the top N companies in my data set on the chart.
= TOPN ( 3, SectionEnrollment, FILTER ( SectionEnrollment, NOT ( ISBLANK ( SectionEnrollment[SchoolYear] ) ) ), DESC ) I am using given above DAX to show top 3 years data without blank in SSAS TOM model but getting error. Please help me!
Hi! Thanks for your videos, are really helpful!! And i want to know if you can post something about the hierarchy slicer. Is not to difficult theme but i haven't found a good explanation. Thank you, again!
HI, very nice videos, maybe you have a nice and simple solution, how to put in slicer to see last 2 /4 /8 /16 months sales development. thank you David
RUTH, ur work is highly appreciated,,,,,,,, kindly; make video for 2021 for simple top 5; bottom 5 and how to connect it with other charts (bar, pie, etc) it would be a great help for the community
Me resulto lograr filtrar por los distintos top. Pero cuando no selecciono nada me devuelve el valor máximo y a ti te devuelve todos los valores. Que hago?
Alguna sugerencia para lo siguiente: Tengo una medida %Ejecución, necesito seleccionar los registros que están en un determinado rango . Una especie de KPI sobre este valor %ejecución. Por ejemplo seleccione solo los que dicho porcentaje sea mayor del 70% etc. Gracias.
+Rodrigo Bedoya Zuluaga Hola Rodrigo, No es fácil contestar a tu pregunta aquí, podrías preguntar en el Power Bi community? Puedes preguntar en español, no es problema :) /Ruth
can you show me how to calculate age against one set date, for example I need to calculate a person age from date of birth to 6/1/2015 , and from date of birth to 3/1/2016 etc
no for example i have a person date of birth 2/1/2001 i want to add few columns to show his age at 2005-6-31 ,2006-6-31, and 2007-6-31 etc for reporting period
+duc thai So you can add a custom column and then subtract the dates? I don't think I am understanding your question. It is better if you publish it on the power bi community where you can show your data. /Ruth
It sounds like you have a specific case in mind, have you tried posting in the power bi community? The solution will depend on how your model looks like and what you are trying to rank. /Ruth
i have three visuals like productwise sales,custwise sals,state wise sales three different visuals so on three visuals using one slicer when i select top3 in slicer auomatically i want display top3 cust,top3states,,,top3 states on my visuals
Hi Ruth, When I try to download the source file for this to shortcut building the machines table, I get this alert: Your connection is not private Attackers might be trying to steal your information from curbal.synology.me (for example, passwords, messages, or credit cards). Learn more NET::ERR_CERT_COMMON_NAME_INVALID Do you know how to resolve this?
@@CurbalEN Hi Ruth, Top n Slicer is working issue is if i select top5 or top 10 than the it filter out correct data but the grand total remain same always how can i fix this please suggest.
I have been trying to follow this tutorial but it is kind of hard because I am a newbie to powerbi and finding the menues you click in is hard because I cant see what you click on sometimes since your screen does not show everything. For instance in minute 1:18 you click on something to see the command line, but I cant find what is it you click on to see the command line. Maybe someone can explain. I am trying to make a top ten in a table with contains products, quantities, values in money, and I would like to make top 10 for both top ten number of products, and top ten highest cost. Please get back to me if anyone knows.
+Figaro Calisthenics Hi Fígaro, yes, I have my Power Bi set up so I have some shortcuts to buttons and also other settings configured like to see the command line. That is not explained in the video so here it goes: to see the command line click on View and then Formula Bar. /Ruth
Thanks for the video bu i keep geting "A single value for column 'Exposure' in table 'Risks' 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" my formula is as follows Measure = RANKX(ALLSELECTED(Risks);Risks[Exposure];;ASC)
+Lawrence That is where you have the issue. You can not have columns in measures without a SUM or COUNT or VALUES or something similar. Add one of those as needed and your measure will work! /Ruth
hi....suppose i have 50 record & there i have used "Rank " to get top 20 records...but rest 30 record count i want as " All Others " .....how to achieve this.....
Great video thanks Ruth. For those experiencing the problem with the incorrect total, there is a work-around here ruclips.net/video/HJdVfYkfhmE/видео.html Hope this helps?
Since I wasn't sure about downloading the source file, I generated dummy data to do the tutorial with, using this query in SQL Server:
with CTE as (
select 1 as ID
union ALL
select ID +1
from cte
where id
Mmmmm that is beyond my comprehension but creative people is the best I know!! :)
Happy holidays and thanks for all your contributions here! Really appreciate it!
I will pin your comment, it will help somebody else :)
/Ruth
That's just how I know how to do it. I'll bet you have a snazzier way to do it with generate series or some other abracadabra() function. =)
By the way, thanks goes to Mister James for supplying a good random function: theycallmemrjames.blogspot.com/2009/04/truly-random-number-in-ms-sql.html
😂😂 I should definitely try!! But next year, this year I am busy shoveling snow ❄️
😂😂😂
/Ruth
Hello Ruth,
Thanks for all you do here, I am actually learning the underlying of DAX through the DAX friday and I actually used this TopN at work yesterday, it kinda felt like a walk in the park.
Thanks again.
Awesome explanation of this function!! Thanks Ruth!!
🎉🎉
Ruth , I love all you videos :) my current dying need is that I want to have top N written on a drill through page. So the dill thru is already passing my selected filters, but now I need to apply top N on this drill thru visual and get the right numbers on one category , and importantly to export right rows in Excel.
Thanks
Look forward
Thank you for making such interactive example
Hi Ruth,
Excellent video .We are using the same in our real time projects.Thanks a lot
Really it is very much helping me to Power BI queries Much Thanks you to share this kind of valuable videos to us
+ashok kumar bandaru You welcome! My pleasure ;)
/Ruth
Hi Ruth,Once again a nice video.
Will u be able to give us such a nice video about the EARLIER function as well?? I found out, that you can do the looping events using the earlier function along with the ALL function,but im not very much aware about it.
Thank you very much Ruth.
Keep it up. Cheers!!!!!
+shashindu lakshitha Hi Shashindu, absolutely!
Expect to see it in the coming weeks, it's on my to- do list now.
Thanks for the suggestion:)
/Ruth
Ruth, your videos are the best! the most amazing! Thank you.
🥳🥳 Thanks!
Hi Ruth, a nice movie. Need help - if my measure is SUM (instead of X), then the total with the selection Top 3, / Top 5, etc. do not give us the correct value (it gives us the max Total). How to solve to get the correct value. Thanks.
Cool video! Thanks Ruth!
Simple and lucid explanation.
Thanks!
/Ruth
Thanks Ruth.
It's the simplest method i've been used
I like simple :)
Gracias, Ruth! muy útil este vídeo... como siempre, sorprendido gratamente!
+Norberto Vera Reatiga y muchas gracias a ti por todo tu apoyo compartiendo los vídeos en las redes sociales ;)
Pasa un buen fin de semana,
/Ruth
Hello!
It is a very good video! I like a lot!
But I'm a little bit confuse with the use of filters. When can I use filter by default just by draging it to the cava, and when I should creat the filter manually? I've been draging all the filters and now I don't know when it is appropiate to create them manually..
I understand if I want to show rankings I should create the filter as you explain, but in which other option I should do it?
Regards!
+Jormary Jackson Hi Jomary, thanks! What do you mean with create the filter manually?
/Ruth
I noticed that you popped into DAX studio to evaluate your expression. I would love to learn more about that as I have been struggling with an expression and was wishing I had a tool to step through my code. Do you have or would you be willing to create a walkthrough of DAX studio for this purpose?
Hi! Yes, i plan to do that on the next vertipaq series. Stay tuned!
/Ruth
Hi Ruth,. I got one question regarding TopN Slicer can we also slice Legend values in Visual
Hi Curbal. Nice vid. Instead of using MAXX to return something in the event that the TOPN slicer isn't selected, I think it might be more efficient leave it with BLANK(), and instead change your approach so thta the AvgMeasurementValue only gets triggered if the filter is applied, and otherwise defaults to just the unadulterated [AvgValue] measure. So use this pattern =IF(HASONEVALUE(TopTable[TopN]) , [Existing AvgMeasurementValue pattern], [AvgValue])
Thanks!!
:)
/Ruth
Thank you for such a great video ! I have 1 question: What if I want to display the correct total for top 3, top 5, top 10 when selected in the slicer ?
Hi, Did you find an answer for the same, I am stuck, and want the SUM total only for the TOP N values. please let me know if you have any resolution.
I enjoy working through these videos.
I think it looks better if you filter the entire table by the TopN slicer rather than just putting blanks in the column.
You can do this by using a measure something like this:
TopNXX =
var SelectedN=
if(HASONEVALUE(TopNTable[TopNValues]),SELECTEDVALUE(TopNTable[TopNValues]),max(TopNTable[TopNValues]))
return
switch(true(),SelectedN=0,1,
[Rank]
Might be, but also my knowledge has increased with time too ☺
Hi, when u select topn 3/5, the avg of machine measure is not appearing. Secondly rank total appearing is 6 everytime which is not correct. Pls let us know if there is second video of this rankx
Very well explained. Thank you
Buenos días, una consulta estoy viendo en la última parte que editas la función: SelectedTopNValue, sin embargo no veo que esa medida se use, solo veo que pusiste la medida: AvgMeasurementValue como columna, podrías explicarme ello. Gracias
Interesting video. For some reason the new measures only work on mine if I use the built in format 'TopN' and not without single quotation marks as yours was. Not sure if this is a version quirk?!
Weird! Can you report it as an issue on issues.powerbi.com?
/Ruth
Hi Ruth, great example. Thanks. But, what about the totals? when you select top 5 or top 10 the total should be filtered, right? how do you do that? many thanks :)
+Jose Silva Hi,
Have you checked this video:
m.ruclips.net/video/ufHOOLdi_jk/видео.html
/Ruth
Thanks Ruth, that has solved my problem!
+Jose Silva Great!! Really happy it helped 😀
/Ruth
cool !! seen a few videos of yours. I will subscribe and hope to see more such valuable content
+Tomas Hujo Thanks for your feedback and subscribing Tomas!
/Ruth
Muchas gracias por compartir, me ayudó bastante!
Perfecto, me alegra oirlo !
Hi Ruth,
Thanks so much for your well-explained video! I would like to ask if there is a way to communicate to all other columns in the table while applying the TopN slicer? Eg if TopN10 is chosen, the table will only show first 10 rows across all columns. Kindly assist. Thank you! :)
Yes, of course , the filter is applied on all columns.
/Ruth
Oh. What I meant is, for example at 8:55 of this video, when slicer TopN5 is selected, only the 2nd column shows up 5 values but the 1st and 3rd columns remain with 10 values each. Is there a way to work dynamically for all 3 columns?
Ok, I need to check the video, I did it a while ago. Hopefully I have time this evening!
/Ruth
Hi Ruth,
In the filter at the beginning of the video there was an option of "All". How does one achieve an "All" option in addition to the Top 3, 5, and 10?
Thanks!
+Patrick Sullivan Hi! It is a setting in the filter itself. Select the filter, go to the formatting pane and the first or the second setting you will be able to turn that on. Sorry for the vague instructions, I am pulling from memory.
/Ruth
Thanks, Ruth!
My pleasure and have a great weekend :)
/Ruth
You too!
Hello, in the total the AVG is AVG for all products and not for the TOP10. please advice how to fix it. thank you, Alona
gracias contigo aprendo mucho sobre medidas dax en power bi sigue asi con mas medidas gracias y bendiciones
+LUIS ALBERTO LOPEZ CHAVEZ Muchas gracias Luis Alberto, seguiré hasta que me pidáis que pare ;)
/Ruth
lala sigue no mas q el conocimeinto no puede parar :)
+LUIS ALBERTO LOPEZ CHAVEZ 😂
/Ruth
Hi,
If you look at video, in initial video description, you can see Full Total is shown 10.56, irrespective of what you select in TOP N filter, however later in Video Total is coming based on your selection or even sometimes its not coming.
Can you please let me know, how to get full total even though any TOP filter is selected.
Regards
Digant
+Digant Desai Hi Digant,
Have you seen this video?
m.ruclips.net/video/ufHOOLdi_jk/видео.html
/Ruth
Hi Ruth, awesome video, can we use Url images for that ranks?..for example salesmans tops with the photo of each worker. Thanks in advance!
hi,
so in the latest version of power bi we can do this automatically by using the filter pane ryt?
Hi Ruth,
Excellent video, many thanks for this. One question, what is we don't want to do Avg Value and rather use the Value column from the Table to display top 5 or top 3 Value.
+Devang Mistry I will have to re-watch the video,
But until I do, there are always multiple ways to solve the same thing, so you are probably right!
/Ruth
Thank u, this is very useful!
Excellent. There is also a TOPN function in DAX which can be used along with SUMMARIZE to achieve the objective.
+Deepak Agrawal Isn't it amazing how many ways there are to do the same thing? :)
/Ruth
Yes, Ruth. That's the beauty of this language. :)
+Deepak Agrawal Agree! :) /Ruth
How can you use this for filtering on basis a measure that is not on the view. for example for the top N inventory items based on value, how can i filter to see the sales or order on hand. so i will have the sales data or order data in the view but the list to be filtered for the top N items that i am holding in stock.
I want to use Top 5 & Bottom 5 with QTD, YTD slicer. Can you help me with this query?
Clever solution...like it. Thank you!
Wondeful to hear :)
Is there a way I can export my topN table with slicer? Whenever I export to excel, it exports the whole rows (sample: 50 rows instead of the top 10)
Thank you!!!
Hola Ruth,
Pequeña sugerencia amistosa de la India,
Cada vez que explique algo nuevo, puede tomar datos que todos puedan entender FÁCILMENTE sin perder demasiado tiempo en eso ... en otras palabras ... puede usar algunos datos de ventas o cualquier información que pueda comprenderse fácilmente. Las personas no deben tomar 5 minutos para entender deteniendo el video una y otra vez solo para comprender los datos de muestra. De lo contrario, tus videos son increíbles. La razón por la que estoy diciendo esto es ... está vaporizando mis niveles de entusiasmo.
Raani Chennamma
De la India
Buena sugerencia gracias :)
/Ruth
Hi i have small doubt while we are displaying Rank value usine rankX DAX function.Some times we are getting same rank for same values right.
For ex:
Machine 11 ---- 0.65 --- 8
Machine 14 ---- 0.65 --- 8
Here, Could you please tell me .I want to display the unique rank based on machine numbers.ofcourse values are same but machines are diffrent right.How could we show the different rank? in this point of scenarios
Very interesting video - Thank you! Unfortunately I did not find here the answer to my problem. Could you point me to your video where you explain how to highlight one record in the table which is the ranking of e.g. cities depending on the chosen name in the slicer using DAX?
Nice video, great tip, thanks
You welcome Diego :)
/Ruth
Many concept covered under a single hood..thanks 👌
You welcome :)
/Ruth
@@CurbalENGracias
👏
/Ruth
It realy helped me, Thank you 👌
Fantastic video
Wow, Thanks :)
/Ruth
Hi Curbal your videos are amazing.
could you please suggest me something on my current query.
where i want to show past ,present and future data based on month selection ,if user selects Mar-19 ,he should see 3 visual one for past one for present one for future with two columns ,product name and its delivery date. kindly suggest.
Hi Pravin,
Hi!
Can you check with the Power bi Community? Here is how and why:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
Que tal Ruth? Excelente video, me sirvió de mucho para este tipo de reportes. Solo que tuve un percance y aún no entiendo porque no funciona. Cuando uso RanX para los items con la tabla "ALLSELECTED('TABLE')", el filtro funciona bien seleccionando cualquier opción, excepto cuando coloco "Seleccionar Todo", en ese momento la medida solo me muestra del rank Nro 1, y obvia el resto. Ya revise el medida que contiene el numero de seleccion del valor del TOPN, y obtiene el valor adecuado, pero la medida del valor a mostrar en la tabla solo me muestra de 1 solo item. Cuando cambio esta opcion en la medida del Rankind, de "AllSelected" a "All". El problema se soluciona. Me gustaría entender por qué tiene ese comportamiento con "AllSelected" con el fin de entenderlo mejor. Me puede ayudar?
Slds cordiales
Hi Curbal,
Thanks for such great videos, Would you post video on how to separate "TOP N" and "Others" in pie chart. Thanks
Absolutely, but if you want a faster answer, try the power bi community :)
/Ruth
Your videos are so helpful, thank you.
Do you have anything on price indexing with dax?
Hi Jessica, thanks!
No, unfortunately nothing. Try in the Power bi Community to get help on your case.
/Ruth
Hi Curbal, Thank you for this video.
I had a question - Suppose the user selects top 3 values is there a way to group(sum/avg) the remaining values of the remaining machines.
For example if we are looking at sales of 12 different machines in a pie chart and you just want to see/focus on the top 3 but also want to see the sales of the remaining 9 machines grouped into another category 'Other' and show it on the pie chart as a 4th slice of the pie, is there a way to do that?
Hi Mustafá, could you post your question in the power bi community? Make sure you post sample data.
/Ruth
Roth, will do so. Have already posted this as an idea as Power BI should add the Top N as a Quick Measure and support the Other categories like ZoomCharts provides. Would appreciate if you and others could support the idea (ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20317618-show-hide-top-x-categories-in-visuals-with-option )
In addition to that I found this post (community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672) which when combined with Top N table/measure logic you explained would address my requirement. Would have liked this to be out of the box for Power BI or at least as Quick Measure
Thanks for sharing the link :)
/Ruth
Agree! Maybe soon?
/Ruth
The 'Average Value' field seems to be an average value per machine, i.e. some group by machine calculation was done. Did the Average value = medianx(data, data[values]) statement do the grouping? I am referring to 1:31 of the video above.
Very clever, thank you for sharing
+Mark Blackburn Thanks Mark!
/Ruth
In last part of VDO, when removed "Rank", order is not by "Rank" but "Machine". Am I miss something/understanding?
Quick question - how you can you get top X N and then the remaining as Others. Ideally we would be able to click into other to then show the next top X n (with any remaining as others ) and so on. is this doable
I have seen it done, but never tried it myself.
Do a quick google search for “topn others power bi” and you will get some suggestions on how to do it.
Happy Friday!!
/Ruth
Any chance of a video ? :-)
Sure, no problem :)
Is on my list now!
/Ruth
:-) !!!!!! thanks Ruth
/mathew
I found this video extremely helpful for something I'm working on. You mention that it is possible to exclude the non displayed values from the matrix totals. I was wondering if you have posted a video on how to do this or can describe how to?
Hi Ruth
What is difference between hasonevalue and selectedvalue why u didn't use selectedvalue
thank you for great video series. I had a question and I would appreciate you could provide answer to it. Is there a way to display only the top n records and not all the records after you apply the filter?
+Ndb Bumb Hi Ndb, which filter are you referring to?
Thanks for watching the series!
/Ruth
hi curbal: thank you for the message. I am referring to after we select top 10 on the slicer thr logic filters top 10 for the average measure column but still shows all the machines and the ranks listed. Is there a way to filter the machines and ranks along with the average measure. hope I am able to explain.
Hi Ndb, I am afraid I still dont understand your question. When you filter by, for example top 3, the list will show you the top3 measurement values and their corresponding machines. What do you mean when you say that it shows all machines?
/Ruth
If I've understood you correctly, then this is what the behaviour was once the rank column was removed from the visual.
Hi, Ruth, thanks for your tutorials I´m learning a lot with it! Does this file is still avaiable for download? I couldn´t find it on Community downloads =(
dax Fridays files are on the DAX Fridays folder!
Thanks, Ruth!
Thank you so much, this video is awesome!!
Could this work if I added percentages instead of whole numbers in the TOPN table?
Hi Ruth, I followed you step by step to do TopN like you did, but, was not success. I replaced [Avg Value] by [AvgMeaurementValue] but noticed that can not update the visual coz have somethings wrong in [AvgMeasurementValue], (I checked again and agian to compare with you formula), consider using VALUE or FORMAT Function to fix it. Have you ever got this error like this? and how to fix it?
+viet vuduc Hi Viet,
Did you download the example file from me?
Does that work for you?
What error are you getting?
/Ruth
Hi Ruth, I learned then tried to apply to my BI Report. I'll do it again then record a clip then send you for more detail.
Thank you for very usefull Topic "DAX Friday".
/Viet
+viet vuduc You welcome! :)
/Ruth
when I select top 3,5,10 I get total of table at bottom , in your case u don't get it , if I need total as well for top 3,5,10 how to get it in table .. thanks
+Yasir ayub Hi Yasir,
Please post your question in the Power BI Community, they will be able to help you!
/Ruth
Thanks... It's very helpful :)
🥳🥳
/Ruth
This works for a table but I am trying to get it to work for a stacked column chart and it does not filter to the top N companies in my data set on the chart.
=
TOPN (
3,
SectionEnrollment,
FILTER ( SectionEnrollment, NOT ( ISBLANK ( SectionEnrollment[SchoolYear] ) ) ), DESC
)
I am using given above DAX to show top 3 years data without blank in SSAS TOM model but getting error.
Please help me!
Can you post this in the power Bi Community and provide some sample data?
/Ruth
Hi! Thanks for your videos, are really helpful!!
And i want to know if you can post something about the hierarchy slicer. Is not to difficult theme but i haven't found a good explanation.
Thank you, again!
Have you seen this one?
m.ruclips.net/video/DW5NZHTZlPw/видео.html
/Ruth
Awesome 🙂
HI, very nice videos, maybe you have a nice and simple solution, how to put in slicer to see last 2 /4 /8 /16 months sales development. thank you David
+David Vanek
Hi David,
Thanks! Maybe this video can help you?
ruclips.net/video/gYbGNeYD4OY/видео.html
/Ruth
Hey Ruth,
Please make videos on Power BI paginated reports.
RUTH, ur work is highly appreciated,,,,,,,, kindly; make video for 2021
for simple top 5; bottom 5 and how to connect it with other charts (bar, pie, etc)
it would be a great help for the community
Use the filter pane for that, easiest way!
@@CurbalEN yes ; true
and how to connect that with entire db for bar charts and pie charts
That is a question for the ppwer bi community. Give as many details as you can to get help :)
@@CurbalEN yes ok.......
ur videos r always motivation 4 me
Thanks!
Cool, But Could you assist me how to get total Value for TopN values
Superb, Thank you!
Awesome!
/Ruth
Me resulto lograr filtrar por los distintos top. Pero cuando no selecciono nada me devuelve el valor máximo y a ti te devuelve todos los valores. Que hago?
Is there a way to show or remove the total that is showing at the bottom..??
How could we modify the measure to say don't show me anything if nothing is selected from TopN? In other words, start with an empty list.
What if i want to display the top 3 ranks as well
Hi Ruth,
How to add All data section in slicer like you ?
Hi, it is a setting on the formatting panel for the slicer called slicer controls or something like that :)
/Ruth
Alguna sugerencia para lo siguiente: Tengo una medida %Ejecución, necesito seleccionar los registros que están en un determinado rango . Una especie de KPI sobre este valor %ejecución. Por ejemplo seleccione solo los que dicho porcentaje sea mayor del 70% etc. Gracias.
+Rodrigo Bedoya Zuluaga Hola Rodrigo,
No es fácil contestar a tu pregunta aquí, podrías preguntar en el Power Bi community?
Puedes preguntar en español, no es problema :)
/Ruth
How do we achieve the reverse (Bottom)?
brilliant! thank you
Thanks!!!
/Ruth
can you show me how to calculate age against one set date, for example I need to calculate a person age from date of birth to 6/1/2015 , and from date of birth to 3/1/2016 etc
+duc thai Hi Where do you want to do it? Power Query or DAX?
Power query in power bi desktop
+duc thai Doesn't it work by subtracting both dates?
/Ruth
no for example i have a person date of birth 2/1/2001 i want to add few columns to show his age at 2005-6-31 ,2006-6-31, and 2007-6-31 etc for reporting period
+duc thai So you can add a custom column and then subtract the dates?
I don't think I am understanding your question.
It is better if you publish it on the power bi community where you can show your data.
/Ruth
Beautyfull, i did not understand why when i select ·Top10" it shows me the Total
could you pls share one more video based on multiple visuals using same slicer
It sounds like you have a specific case in mind, have you tried posting in the power bi community?
The solution will depend on how your model looks like and what you are trying to rank.
/Ruth
i have three visuals like productwise sales,custwise sals,state wise sales three different visuals so on three visuals using one slicer when i select top3 in slicer auomatically i want display top3 cust,top3states,,,top3 states on my visuals
Thanks, please post the question in the power bi community,you will get help in no time :)
/Ruth
Hi Ruth,
When I try to download the source file for this to shortcut building the machines table, I get this alert:
Your connection is not private
Attackers might be trying to steal your information from curbal.synology.me (for example, passwords, messages, or credit cards). Learn more
NET::ERR_CERT_COMMON_NAME_INVALID
Do you know how to resolve this?
Hi Mark, issues with my SSL certificate . Click advance and then proceed and you will be fine, promise!
/Ruth
Cab we do this is power pivot if yes than how to do the data connectivity
Hi,
You should be able to follow the same steps.
/Ruth
@@CurbalEN Hi Ruth,
Top n Slicer is working issue is if i select top5 or top 10 than the it filter out correct data but the grand total remain same always how can i fix this please suggest.
Have you seen this?
ruclips.net/video/ufHOOLdi_jk/видео.html&vl=en
/Ruth
@@CurbalEN Ruth,
I have already seen this video same condition i m using with TOP n slicer but its is not giving correct total.
Please help me out.
For support, please post in the Power BI community. Make sure you explain your case in detail to get help.
/Ruth
Спс!!! Очень познавательно. 😶
пожалуйста!
/Ruth
can we do this is power pivot?
I have been trying to follow this tutorial but it is kind of hard because I am a newbie to powerbi and finding the menues you click in is hard because I cant see what you click on sometimes since your screen does not show everything. For instance in minute 1:18 you click on something to see the command line, but I cant find what is it you click on to see the command line. Maybe someone can explain. I am trying to make a top ten in a table with contains products, quantities, values in money, and I would like to make top 10 for both top ten number of products, and top ten highest cost.
Please get back to me if anyone knows.
+Figaro Calisthenics Hi Fígaro, yes, I have my Power Bi set up so I have some shortcuts to buttons and also other settings configured like to see the command line.
That is not explained in the video so here it goes: to see the command line click on View and then Formula Bar.
/Ruth
Rank doesn't work without all, right?
Thanks for the video bu i keep geting "A single value for column 'Exposure' in table 'Risks' 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" my formula is as follows Measure = RANKX(ALLSELECTED(Risks);Risks[Exposure];;ASC)
+Lawrence Hi Lawrence! Is Risks[Exposure] a column or a measure?
/Ruth
Hi Ruth, its a column i think, on risk log its a calculated column value between =Probability (%)* Impact (1-10).
+Lawrence That is where you have the issue. You can not have columns in measures without a SUM or COUNT or VALUES or something similar.
Add one of those as needed and your measure will work!
/Ruth
thanks Ruth i added SUM(Risks[Exposure])) and it worked
+Lawrence Excellent!
/Ruth
hi....suppose i have 50 record & there i have used "Rank " to get top 20 records...but rest 30 record count i want as " All Others " .....how to achieve this.....
Hi!
Please check this video on how to get Power BI support:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
@@CurbalEN I hav seen this video but ...i didnt find solution for my question
Post your question here: community.powerbi.com to get help.
/Ruth
I like your voice haha
Should start a podcast!
/Ruth
@@CurbalEN yes hahaha,
really unclear explanation on the last part (Maxx....)
Can someone help?
Great video thanks Ruth. For those experiencing the problem with the incorrect total, there is a work-around here ruclips.net/video/HJdVfYkfhmE/видео.html
Hope this helps?
+Jonny Parsons Hi Jonny,
I have a video on that too, but thanks for the resource :)
/Ruth
The Video is broken!