Hi Avi..went through the file and replicated it in my file..and it works. It is surprising - how simple things can be if you understand the concept. I was thinking it to be a long nested dax expression.:) . Thank you so much!!
You're most welcome Ankit... I know, that's probably human nature :). Power On! Team Avi • Power BI Tutorial goo.gl/KRRkPG • Make sure to join me live on TalkPowerBI Fridays th-cam.com/users/powerbiprolive
Nice explanation, thank you for going step by step and showing what was 'wrong' with each previous step. Helps to have a good understanding of what the DAX expression is actually trying to do.
Thank you so much for this! You clearly explained how to define the part of the RANKX function. No other page I've visited explained how to do rank within a group better than this one.
Marisabel, Thank you so much for your comment. I really appreciate it. If you get stuck anywhere else, join us LIVE on Fridays for our TalkPowerBI show each week. Link below. ⚡Power On!⚡ / Avi ★ Power BI Beginner Tutorial: th-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★ ★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Thank you!! You just saved me a trip to the mental institution. I looked at other videos and followed their directions to the T and could not get RANKX to work. Until I viewed your video. Gracias!!
I just watched this again to remember how to do it. Your I.C.A.N. method to understand filters and contexts is making more and more sense each time. Thank you very much for you amazing work!!
Glad it helped. If you'd like to learn more about Power BI , here is a link about our structured course on this Webinar 👉 web.learnpowerbi.com/waitlist-invite/ ^RM
Lots of potential ways to use ranking/"subrankikng" within groups -- such as Categories / Subcategories. For example in retail, Product categories/sub Categories. For demographics, you could potential use to rank gender, race, age, etc. (or even political party etc. such as with voter registration and voter turnouts).
Yes! Good suggestions there Kevin! And you can show multiple rank measures at the same time. e.g. for a Customer report. You can show - here is their Worldwide Rank #4562 - here is their Rank within Country #234 - here is their rank in State #23 You can even *TREND* their rank over time. I love seeing that. Power On! / Avi Join me LIVE for #TalkPowerBI Fridays and ask your Power BI questions th-cam.com/users/powerbiprolive
Yes exactly! Very useful when you are "slicing" and/or comparing -- for example a store location might be #1 or #2 with District, but how do they "Rank: in comparison for Region, Company etc?
Indeed. It is amazing how Power BI can unlock a series of questions. I equate it to peeling the onion. And there is always the next layer. You have to love the process too :-)
Hello Avi, great video. Thank you for that. I wonder if you can help with a little variation. I need to create a similar visual as the one you are referring to in your video. I have followed numerous videos, including yours and I can't get it to work. The visual is a table with a list of Grouping members and a single select slicer for the Brand. I only need to see the ranking for the Brand in each Grouping. I have identified the problem to the fact that measure by which the ranking is done is different for each of Grouping members. This measure calculates a total for a date range dynamically calculated for the Brand selected by the slicer (i.e. first day a condition is passed + 5 days). I can get the measure to calculate the correct totals, but this doesn't follow through to the rank measure. My control tables work correctly when the date range is chosen manually (I have done loads of manual checking), but I need this to be dynamic. Any idea, how to achieve this? Any help will be greatly appreciated as I have been fighting this for weeks :( Thanks a lot, Jana
I have a survey where we asked in the rank of same products as 5 questions - Rank 1 - A/B/C/D Rank 2 - A/B/C/D Rank 3 - A/B/C/D Rank 4 - A/B/C/D Any easy way to calculate these options A,B,C,D on the basis of rank weight?
Hi Avi, Am facing similar type of scenario in my case i want to Rank grouping column i tried Rankx(All(grouping),totalpoint) it’s giving output when am using groping column and points column if am adding brand also in the table am getting 1 for all the data
Thanks Avi for the video. I was stuck with a simple rank problem and that was solved by "ALL". :) A question: How can we make this formula more robust to anticipate new "grouping", i.e. data fields, added to the table? E.g. I want to rank by Product ID. But sometimes, I would put Category, Sub Category, Product Class, etc to the table. My silly thoughts was to create a "new measure" for different scenarios, which is ineffective. Appreciate your advice on this. 🙏
Hello Sr. Avi, thanks for your video, i have a little problem with RANKX() in my dataset. I have a table with 2 levels: companies and customers. the numeric variable is the Quantity sold. I need to do the same thing that you do at minute 4:33 (ranking of customers for each company) but instead of using a calculated measure, I need to do it as a column in the data table because I need to rename some clients afterwards that are after the top 15 ° as "Others". Thank you for reading
Hello, I am trying the same thing for my dataset which having scoring of employees for year and all 4 quarters. I want to rank employee as per average score for quarter.but getting error ' Single value for column for column in table can not be determined.
Hi I have an issue with Power BI, I have a table with technicians and another table with some invoices related, I'd like to get the top 1 product for each technician using rank but I'm unable to do it, if I do it as a measure and put it on a visual it works but when doing on a calculated table or something like that it displays only 1's for all records on Rank
Im using RANKX(ALLSELECTED because I want to rank base on what is shown on my visual. Sample, I filter on a visual to only get the top 15 and I want those ranked from 1 to 12. Using ALLSELECTED work but it takes a long time to load the result since its checking each row of the entire table. Is there an alternative for this? I can only imagine the impact when we reach a 500k rows or more.
I have data which is at territory X product level, and i want to calculate rank the product ranking for every category separately based on net sales. The following throws only rank 1 for every entry Rank = RANKX(ALL('Table Name'[Territory]),SUM('Table Name'[NET_SALES])) Not sure what am missing, new to power BI so question might be very naive!
hello sir, thanks for your video and tutorials. I'd like to ask you for a favor. How do I rank stores and product names by Sales in matrix (visualization). In the report page, there would be a filter with Countries, in the matrix there would be Store names and product names and I would like click on one of the countries, then I would be able to see Ranked stores, when I click "+" (plus) on one of the stores product names would also be ranked. Could you help me with that please?
Hi Avi, I have data where my sales are available by product, category, Sales Amount, profit, and sell date. I want to add ranking on the basis of a date filter (using a slicer between two dates). e.g. I have a summary that shows the Product name, product category, total sales. and now I have used slicer and added Sell date from 1st Oct to 5 Oct. my rank function would be on profit change percentage (5th October profit - 1st October profit)/1st October profit and then ranking on newly created measure "Profit change", Thank you in advance :)
Hello Avi, I'm trying to do a slicer that defaults to show the report's information for the prior workday's date. So for example, on the current day, I'm wanting to have a default slicer that shows the production for the prior workday (This is not just yesterday because there can be weekends and holidays that the date would be blank). I was trying to use rankx to show the max date of today (being 1) then select 2 (prior day) as the default filter. How would you do this or is there an easier way? Thank you, Rdale
Hi Avi - I have multiple duplicates in my dataset, what I would like to do is rank them by date DESC and rank the latest with the rank of 1. Thanks in advance!
Hi Avi, I have question I know you can help me. I also creating a Stack rank report on my team. Basically, there are lots of metrics that I need to consider and each one of those are needed to be rank and add them all in total points then rank them again using the total points. I have built it already but my reports load very slow.
Hi Avi, I have simular data and i followed your steps. But the rank within group still gives a one everywhere although i added the all funtion aroudn the value. My formula is: Rank within Race = rankx(ALL('WM_SCORES'[RaceID]) , [TotalScore]) Totalscores is a measure that says sum(Score) in the table. This because just score didn't work and i noticed your totalscore also was a measure can you help me out? C.
Hello Sir, I stuck in middle of something. I have different dates in 1 column and that dates are duplicate as well in a column and there are values corresponding to that date column. My requirement is it should automatically take latest 5 dates and apply pivot on that. Post that last column should get subtracted from second column and that values should store in a new column
Hello, this works perfectly, but whenever i am applying some other explicit filer, (not related to these columns), Example: show only telecom companies (Column which classifies the companies in to various industries), Then all the rank just changes completely, and it is completely wrong. I have 4-5 other filters which users can apply, and the ranking should always calculate based on which filter is selected. so this doenst work me, any solutions pleae?
Hi there, i have a measure that calculates the % of each product sold based on total sold. now i need to create a rank based on this percentages. 5 for (100-80), 4 for (80-60), etc..Im trying to create a new measure with "if" but i cant create a measure based on other measure, or can i ? any idea? thanks a lot
Hi Avi, How can I add 2 columns for grouping? I have sales amounts by agent and date, when I only use one colum for grouping, it is giving me a different result as ranking when date change. Thanks!!
Hi Avi, thanks so much for this, its worked for me! One quick question, looking at the final table is there a way to get an overall rank for each grouping based on all of its ranks? A final rank or rank of ranks?
Hi, How can we show the top 3 sale outlet in each month in Power BI? Generally the output would be something similar like below: top | Jan | Feb | Mar | Apr ----------------------------------------------------------------------- 1 | Amazon | Flipkart | Shop3 | Shop4 2 | Shop5 | Shop6 | Shop7 | Shop8 3 | Shop11 | Shop15 | Shop20 | Shop21 Lets assume the data is huge
Helli Avi, Thank you so much. You solved my first problem. Could you suggest me on how to use this rank value in slicer. As Slicer in PowerBI does not take measure. Any suggestions would be helpful :)
Hi Avi .. doesn't seem to work .. complaining about the measure? A single value for column 'Volume (Shipment)' in table 'EDC Container + PO' 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.
Hi Giancarlo Gonzales. Glad it helped. And if you have any additional questions you can get them answered in our LIVE TalkPowerBI Q&A show. 👉 www.learnpowerbi.com/talk#qa ⚡Power On!⚡ / Avi ★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★ ★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
Hi Avi! I have a question about ranking within each date. I have 'ReportDate' -date time field in the format till mili seconds. Each day contains 2 snapshots of data (e.g. on the 22 of September we have 2 snapshots on 04:37 AM and on 09:15 AM). I need to filter the first snapshot for each of the day ( in this case exclude 04:37 AM). This approach should be expanded on all dataset, so that we can exclude first snapshots for each day. I wrote: Date Rank = RANKX(all(Dates[ReportDate].[Day]), Dates[ReportDate].[Day],,ASC) but it does not work correctly. Moreover it did not recalculate 1,2 rank for each date. Could you advise me what should I do to filter first snapshots? Thank you.
Hey Anastasia, Great question and really fun scenario. I LOVE LOVE LOVE solving real world challenges. Real world is messy but it is interesting. AdventureWorks/Contoso/Fabrikam can't even come close. So first thoughts - It's always a balance between solving something in Query Editor or DAX. There are best practices Query Editor = is the kitchen, where you clean, shape, transform your data DAX = is where you do all your calculation But you need to evaluate for each case. Looking at your scenario, my first question is: *Can you solve this in Query Editor?* i.e. Filter out the First snapshot from the data itself? Now that would depend on whether you do not need the first snapshot a) in ANY scenario or b) just need to exclude that for some scenario If it's a) exclude it in the Query Editor If it's b) you need a DAX Solution. Let me know. If you stay stuck see if you can join me on my LIVE #TalkPowerBI show on Fridays and ask your question there. th-cam.com/users/powerbiprolive Power On! / Avi
Got it...tell me a little bit more - Is Ranking the End Goal? You just want to show Ranking for each Calendar Day after you filter out the first Snapshot - Or Ranking is just a stepping stone for you? Are you trying to use Ranking to just filter things out? And then do some math/calculation. E.g. After you use Ranking to filter out the first snapshot, then you need to see the SUM/MAX/MIN/AVG/STDDEV for the 2nd Snapshots If you can prep a simple sample dataset (I usually do it quickly in Excel) and bring it to the next LIVE TalkPowerBI we can try solving it there th-cam.com/users/PowerBIProlive
is the pbix file available? would like to know how you did the chart. This was helpful, I just can't figure out a way to show all my measures and rankings. Thanks!
Hey Britni, Glad to know you got the file. If you get stuck or have any questions..ask me in my next TalkPowerBI. I go live each Friday. Link below.⚡Power On!⚡ / Avi ★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Helo, first of all thanks for the tutorial. I have a question. In your example there is a moment where there is two rows with the same value, then the ranking number is the same (7 in this case) Is there any way to add a second critery for the Ranking? The problem is that if you only want to show ten rows in a table, and, like in your example, two of them have the same value, the table will show eleven rows, and that is not that i am needing. Thanks!!!
Hi Oscar, I have solved this problem by adding a Dense option which is similar to the concept of Oracle. EX: RANKX(ALL(Tablename(column_name),[Count],,DESC,Dense)
You are welcome Pratik and thank you for the compliment. ⚡Power On!⚡ / Avi ★ Power BI Beginner Tutorial: th-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★ ★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Hi Avi, I wanted a ranking over a calculated column like '% Success' , which is just a Calcualtion as [No of Product Sold] / [No of Customer Interested]. But it is giving me error : Column '% Success' in table 'Facts' cannot be found or may not be used in this expression. Table Fact : Product - # Interest - # Sales Prd 01 - 500 - 30 Prd 02 - 200 - 12 Prd 03 - 700 - 40 Prd 04 - 100 - 10 Prd 05 - 900 - 42 I have added a calculated Column '% Success' = # Sales / # Interest Which became Product - # Interest - # Sales - %Success Prd 01 - 500 - 30 - 6% Prd 02 - 200 - 12 - 6% Prd 03 - 700 - 40 - 5.7% Prd 04 - 100 - 10 - 10% Prd 05 - 900 - 42 - 4.7% Now I want a ranking as Product - Rank Prd 04 - 1 Prd 02 - 2 [Although 1 is same but priority should be higher which was low interest high sales] Prd 01 - 3 Prd 03 - 4 Prd 05 - 5 Hope on your expart solution on this matter. Regards, Sukhomoy
+Sukhomoy Bhattacharya Join me in my next TalkPowerBI Live on Fridays and I should be able to help you. Link below ⚡Power On!⚡Avi ** Join me for next #TalkPowerBI Live on Fridays www.TH-cam.com/PowerBIPro/Live **
Hello Avi, thank you for the informative tutorial on ranking... is there a way to work around a tie in rank by subranking the tied elements based on a secondary metric rather than specifying Dense or Skip in the RANKX formula?
+Kenny Lim Great question, I was thinking using FIRSTNONBLANK() but i don't think that would fully solve that. Let me think about it. Maybe bring it up in one of the live sessions and we can explore your specific scenario there. ⚡Power On!⚡Avi ** Join me for next #TalkPowerBI Live on Fridays www.TH-cam.com/PowerBIPro/Live **
@@PowerBIPro 5 years on, I now need this question answered. I have duplicate values that I want made sequential. Was 5 years enough time to think about it? haha
Hi Erick Chicas. Thank you for the compliment! ⚡Power On!⚡ / Avi ★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★ ★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Hi ANIKET, The questions seems a little bit involved. The best option to get help on this may be joining one of my LIVE Q&A Calls. See 👉 www.learnpowerbi.com/talk#qa But going by what you said, it seems you have already ranked items in a table. e.g. USA = 1 Canada = 2 Germany = 3 etc. Then you are perhaps looking to find, What are the [Sales] for the Country Ranked #3. That should be possible using CALCULATE, FILTER and RANKX Measure. But I would not be able to dive into it here. We can work through on the LIVE Q&A Call if you join. We hold that call, once each month. Otherwise, for immediate & private help with Power BI Consulting or Training, you can: 👉 Contact us for at www.learnpowerbi.com/contact 👉 Or by going to our website www.learnpowerbi.com and clicking the "Contact Me" Button ⚡Power On!⚡ / Avi ★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
"The value for ABC cannot be determined. Either the column doesn't exist or there is no current row for this column" Trying to get rid off from this error from last 24 hrs. Error came while performing Rank function. Please help @Avi Singh
Thank you for your feedback. If you'd like to learn more about Power BI , here is a link about our structured course on this Webinar 👉 web.learnpowerbi.com/waitlist-invite/ ^RM
Kevin! Thanks so much for your kind words. I am humbled and happy to be in a position to help others. This is the biggest joy anyone can experience. Power On! / Avi Join me LIVE for #TalkPowerBI Fridays and ask your Power BI questions th-cam.com/users/powerbiprolive
Hi Jason Woodward. You're welcome! ⚡Power On!⚡ / Avi ★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★ ★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
hello, i want ro rank transaction dates as below based on invoices belonging to the each customer, how do i go about it. customer id invoice id transaction date RANKING A 1234 10/09/2022 1 A 67585 09/09/2022 2 B 7585 24/10/2022 1 B 75805 26/10/2022 2 C 489055 05/02/2022 - 1 D 4855 06/02/2022 1
Hi Avi..went through the file and replicated it in my file..and it works. It is surprising - how simple things can be if you understand the concept. I was thinking it to be a long nested dax expression.:) . Thank you so much!!
You're most welcome Ankit... I know, that's probably human nature :). Power On! Team Avi
• Power BI Tutorial goo.gl/KRRkPG • Make sure to join me live on TalkPowerBI Fridays th-cam.com/users/powerbiprolive
Nice explanation, thank you for going step by step and showing what was 'wrong' with each previous step. Helps to have a good understanding of what the DAX expression is actually trying to do.
Thank you so much for this! You clearly explained how to define the part of the RANKX function. No other page I've visited explained how to do rank within a group better than this one.
Marisabel, Thank you so much for your comment. I really appreciate it.
If you get stuck anywhere else, join us LIVE on Fridays for our
TalkPowerBI show each week. Link below.
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: th-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Thank you!! You just saved me a trip to the mental institution. I looked at other videos and followed their directions to the T and could not get RANKX to work. Until I viewed your video. Gracias!!
Thanks for the inspiring feedback. I really appreciate it. Power On! / Team Avi
I just watched this again to remember how to do it. Your I.C.A.N. method to understand filters and contexts is making more and more sense each time. Thank you very much for you amazing work!!
My pleasure Raul!
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Cheers Avi.. Just what i was looking for , clear , concise and easy to follow..
Glad it helped. If you'd like to learn more about Power BI , here is a link about our structured course on this Webinar
👉 web.learnpowerbi.com/waitlist-invite/
^RM
Lots of potential ways to use ranking/"subrankikng" within groups -- such as Categories / Subcategories. For example in retail, Product categories/sub Categories. For demographics, you could potential use to rank gender, race, age, etc. (or even political party etc. such as with voter registration and voter turnouts).
Yes! Good suggestions there Kevin!
And you can show multiple rank measures at the same time.
e.g. for a Customer report. You can show
- here is their Worldwide Rank #4562
- here is their Rank within Country #234
- here is their rank in State #23
You can even *TREND* their rank over time. I love seeing that.
Power On! / Avi
Join me LIVE for #TalkPowerBI Fridays and ask your Power BI questions th-cam.com/users/powerbiprolive
Yes exactly! Very useful when you are "slicing" and/or comparing -- for example a store location might be #1 or #2 with District, but how do they "Rank: in comparison for Region, Company etc?
Indeed. It is amazing how Power BI can unlock a series of questions. I equate it to peeling the onion. And there is always the next layer. You have to love the process too :-)
Thank you, I was struggle with this DAX function
Power on! ^RM
Hello Avi, great video. Thank you for that.
I wonder if you can help with a little variation. I need to create a similar visual as the one you are referring to in your video. I have followed numerous videos, including yours and I can't get it to work.
The visual is a table with a list of Grouping members and a single select slicer for the Brand. I only need to see the ranking for the Brand in each Grouping.
I have identified the problem to the fact that measure by which the ranking is done is different for each of Grouping members. This measure calculates a total for a date range dynamically calculated for the Brand selected by the slicer (i.e. first day a condition is passed + 5 days). I can get the measure to calculate the correct totals, but this doesn't follow through to the rank measure.
My control tables work correctly when the date range is chosen manually (I have done loads of manual checking), but I need this to be dynamic.
Any idea, how to achieve this? Any help will be greatly appreciated as I have been fighting this for weeks :(
Thanks a lot,
Jana
I have a survey where we asked in the rank of same products as 5 questions -
Rank 1 - A/B/C/D
Rank 2 - A/B/C/D
Rank 3 - A/B/C/D
Rank 4 - A/B/C/D
Any easy way to calculate these options A,B,C,D on the basis of rank weight?
Hi Avi, Am facing similar type of scenario in my case i want to Rank grouping column i tried Rankx(All(grouping),totalpoint) it’s giving output when am using groping column and points column if am adding brand also in the table am getting 1 for all the data
Thanks Avi for the video.
I was stuck with a simple rank problem and that was solved by "ALL". :)
A question:
How can we make this formula more robust to anticipate new "grouping", i.e. data fields, added to the table?
E.g. I want to rank by Product ID. But sometimes, I would put Category, Sub Category, Product Class, etc to the table. My silly thoughts was to create a "new measure" for different scenarios, which is ineffective. Appreciate your advice on this. 🙏
Great! If the the table has a column of different dates; would it be possible to do what you did and add the date sort?
Hello Sr. Avi, thanks for your video, i have a little problem with RANKX() in my dataset.
I have a table with 2 levels: companies and customers. the numeric variable is the Quantity sold.
I need to do the same thing that you do at minute 4:33 (ranking of customers for each company) but instead of using a calculated measure, I need to do it as a column in the data table because I need to rename some clients afterwards that are after the top 15 ° as "Others".
Thank you for reading
Hello,
I am trying the same thing for my dataset which having scoring of employees for year and all 4 quarters. I want to rank employee as per average score for quarter.but getting error ' Single value for column for column in table can not be determined.
Hi I have an issue with Power BI, I have a table with technicians and another table with some invoices related, I'd like to get the top 1 product for each technician using rank but I'm unable to do it, if I do it as a measure and put it on a visual it works but when doing on a calculated table or something like that it displays only 1's for all records on Rank
Im using RANKX(ALLSELECTED because I want to rank base on what is shown on my visual. Sample, I filter on a visual to only get the top 15 and I want those ranked from 1 to 12. Using ALLSELECTED work but it takes a long time to load the result since its checking each row of the entire table. Is there an alternative for this? I can only imagine the impact when we reach a 500k rows or more.
I have data which is at territory X product level, and i want to calculate rank the product ranking for every category separately based on net sales. The following throws only rank 1 for every entry
Rank = RANKX(ALL('Table Name'[Territory]),SUM('Table Name'[NET_SALES]))
Not sure what am missing, new to power BI so question might be very naive!
hello sir, thanks for your video and tutorials. I'd like to ask you for a favor. How do I rank stores and product names by Sales in matrix (visualization). In the report page, there would be a filter with Countries, in the matrix there would be Store names and product names and I would like click on one of the countries, then I would be able to see Ranked stores, when I click "+" (plus) on one of the stores product names would also be ranked. Could you help me with that please?
Hi Avi, I have data where my sales are available by product, category, Sales Amount, profit, and sell date. I want to add ranking on the basis of a date filter (using a slicer between two dates). e.g. I have a summary that shows the Product name, product category, total sales. and now I have used slicer and added Sell date from 1st Oct to 5 Oct. my rank function would be on profit change percentage (5th October profit - 1st October profit)/1st October profit and then ranking on newly created measure "Profit change", Thank you in advance :)
Hello Avi, I'm trying to do a slicer that defaults to show the report's information for the prior workday's date. So for example, on the current day, I'm wanting to have a default slicer that shows the production for the prior workday (This is not just yesterday because there can be weekends and holidays that the date would be blank). I was trying to use rankx to show the max date of today (being 1) then select 2 (prior day) as the default filter. How would you do this or is there an easier way? Thank you, Rdale
Hi Avi -
I have multiple duplicates in my dataset, what I would like to do is rank them by date DESC and rank the latest with the rank of 1. Thanks in advance!
Hi Avi, I have question I know you can help me. I also creating a Stack rank report on my team. Basically, there are lots of metrics that I need to consider and each one of those are needed to be rank and add them all in total points then rank them again using the total points. I have built it already but my reports load very slow.
Hi Avi,
I have simular data and i followed your steps.
But the rank within group still gives a one everywhere although i added the all funtion aroudn the value.
My formula is:
Rank within Race = rankx(ALL('WM_SCORES'[RaceID]) , [TotalScore])
Totalscores is a measure that says sum(Score) in the table.
This because just score didn't work and i noticed your totalscore also was a measure
can you help me out?
C.
Hello Sir,
I stuck in middle of something.
I have different dates in 1 column and that dates are duplicate as well in a column and there are values corresponding to that date column. My requirement is it should automatically take latest 5 dates and apply pivot on that. Post that last column should get subtracted from second column and that values should store in a new column
Hello,
this works perfectly, but whenever i am applying some other explicit filer, (not related to these columns), Example: show only telecom companies (Column which classifies the companies in to various industries), Then all the rank just changes completely, and it is completely wrong. I have 4-5 other filters which users can apply, and the ranking should always calculate based on which filter is selected. so this doenst work me, any solutions pleae?
Hi there, i have a measure that calculates the % of each product sold based on total sold. now i need to create a rank based on this percentages. 5 for (100-80), 4 for (80-60), etc..Im trying to create a new measure with "if" but i cant create a measure based on other measure, or can i ? any idea? thanks a lot
Is there a way to reverse the ranking? Meaning, if I am looking at cost per lead, I want the lowest cost as #, like scoring in golf.
Yes, specify the 4th parameter as "ASC".
Hi Avi, one quick question.. How did you calculate the total points column?
Hi Avi,
How can I add 2 columns for grouping?
I have sales amounts by agent and date,
when I only use one colum for grouping, it is giving me a different result as ranking when date change.
Thanks!!
Great video. Rankx has been difficult
Hi Avi, thanks so much for this, its worked for me! One quick question, looking at the final table is there a way to get an overall rank for each grouping based on all of its ranks? A final rank or rank of ranks?
What if there are multiple Group By Column from different Lookups. For example, Rank of Sales By Department By Month!
Hi, I copied this formula the Dax doesn't detect the column value that you've got in purple. The column is numeric. Any help would be appreciated.
Hi, can you make a powerbi formula that will rank a 4 column of sorting that will avoid tie rank in the final ranking. Thank you.
Try this formula:
= RANKX(ALL(), [Column1] + [Column2] + [Column3] + [Column4], , DESC) + COUNTIFS(ALL(), [Column1] + [Column2] + [Column3] + [Column4] = EARLIER([Column1] + [Column2] + [Column3] + [Column4])) - 1
Hi,
How can we show the top 3 sale outlet in each month in Power BI? Generally the output would be something similar like below:
top | Jan | Feb | Mar | Apr
-----------------------------------------------------------------------
1 | Amazon | Flipkart | Shop3 | Shop4
2 | Shop5 | Shop6 | Shop7 | Shop8
3 | Shop11 | Shop15 | Shop20 | Shop21
Lets assume the data is huge
Helli Avi, Thank you so much. You solved my first problem. Could you suggest me on how to use this rank value in slicer. As Slicer in PowerBI does not take measure. Any suggestions would be helpful :)
Hi!
What happens if I do not have the totals ... can I calculate them within the same formula? or i need to create a new table?
Thanks!
Hi Avi .. doesn't seem to work .. complaining about the measure?
A single value for column 'Volume (Shipment)' in table 'EDC Container + PO' 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.
thank you so much!!! I needed to solve this issue
Hi Giancarlo Gonzales. Glad it helped. And if you have any additional questions you can get them answered in our LIVE TalkPowerBI Q&A show. 👉 www.learnpowerbi.com/talk#qa
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
Hello Avi, is it possible to include weeks in the matrix to show have the rankings change over time?
Thank you. Its very helping
Hi Avi! I have a question about ranking within each date. I have 'ReportDate' -date time field in the format till mili seconds. Each day contains 2 snapshots of data (e.g. on the 22 of September we have 2 snapshots on 04:37 AM and on 09:15 AM). I need to filter the first snapshot for each of the day ( in this case exclude 04:37 AM). This approach should be expanded on all dataset, so that we can exclude first snapshots for each day. I wrote: Date Rank = RANKX(all(Dates[ReportDate].[Day]), Dates[ReportDate].[Day],,ASC) but it does not work correctly. Moreover it did not recalculate 1,2 rank for each date. Could you advise me what should I do to filter first snapshots? Thank you.
Hey Anastasia,
Great question and really fun scenario. I LOVE LOVE LOVE solving real world challenges. Real world is messy but it is interesting. AdventureWorks/Contoso/Fabrikam can't even come close.
So first thoughts
- It's always a balance between solving something in Query Editor or DAX. There are best practices
Query Editor = is the kitchen, where you clean, shape, transform your data
DAX = is where you do all your calculation
But you need to evaluate for each case.
Looking at your scenario, my first question is: *Can you solve this in Query Editor?*
i.e. Filter out the First snapshot from the data itself?
Now that would depend on whether you do not need the first snapshot
a) in ANY scenario or
b) just need to exclude that for some scenario
If it's a) exclude it in the Query Editor
If it's b) you need a DAX Solution.
Let me know. If you stay stuck see if you can join me on my LIVE #TalkPowerBI show on Fridays and ask your question there. th-cam.com/users/powerbiprolive
Power On! / Avi
@@PowerBIPro thanks for the response! I have the 'b' scenario ☺
Got it...tell me a little bit more
- Is Ranking the End Goal? You just want to show Ranking for each Calendar Day after you filter out the first Snapshot
- Or Ranking is just a stepping stone for you? Are you trying to use Ranking to just filter things out? And then do some math/calculation. E.g. After you use Ranking to filter out the first snapshot, then you need to see the SUM/MAX/MIN/AVG/STDDEV for the 2nd Snapshots
If you can prep a simple sample dataset (I usually do it quickly in Excel) and bring it to the next LIVE TalkPowerBI we can try solving it there
th-cam.com/users/PowerBIProlive
Hi Avi
Thank you for the explanation.
I would like a rank as a column because I need to do a filter with it in a tachometer. Is It possible?
Thank you
is the pbix file available? would like to know how you did the chart. This was helpful, I just can't figure out a way to show all my measures and rankings. Thanks!
nevermind, got it!
Hey Britni,
Glad to know you got the file. If you get stuck or have any questions..ask me in my next TalkPowerBI. I go live each Friday. Link below.⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Helo, first of all thanks for the tutorial. I have a question. In your example there is a moment where there is two rows with the same value, then the ranking number is the same (7 in this case) Is there any way to add a second critery for the Ranking? The problem is that if you only want to show ten rows in a table, and, like in your example, two of them have the same value, the table will show eleven rows, and that is not that i am needing. Thanks!!!
Hi Oscar, I have solved this problem by adding a Dense option which is similar to the concept of Oracle. EX: RANKX(ALL(Tablename(column_name),[Count],,DESC,Dense)
Thank you so much for the great explanation!!
You are welcome Pratik and thank you for the compliment.
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: th-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Hi Avi, thanks for this video. It’s helped lot . Could you help me How to make table as a dynamic slicer . Is it possible
Hi Avi, I wanted a ranking over a calculated column like '% Success' , which is just a Calcualtion as [No of Product Sold] / [No of Customer Interested]. But it is giving me error : Column '% Success' in table 'Facts' cannot be found or may not be used in this expression.
Table Fact :
Product - # Interest - # Sales
Prd 01 - 500 - 30
Prd 02 - 200 - 12
Prd 03 - 700 - 40
Prd 04 - 100 - 10
Prd 05 - 900 - 42
I have added a calculated Column '% Success' = # Sales / # Interest
Which became
Product - # Interest - # Sales - %Success
Prd 01 - 500 - 30 - 6%
Prd 02 - 200 - 12 - 6%
Prd 03 - 700 - 40 - 5.7%
Prd 04 - 100 - 10 - 10%
Prd 05 - 900 - 42 - 4.7%
Now I want a ranking as
Product - Rank
Prd 04 - 1
Prd 02 - 2 [Although 1 is same but priority should be higher which was low interest high sales]
Prd 01 - 3
Prd 03 - 4
Prd 05 - 5
Hope on your expart solution on this matter.
Regards,
Sukhomoy
+Sukhomoy Bhattacharya Join me in my next TalkPowerBI Live on Fridays and I should be able to help you. Link below
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.TH-cam.com/PowerBIPro/Live **
can you make one video for ranks using Different Dimensions and Different facts on top of we need to display Top 50 ranks by total amount
Thank you so much, i was struggling with this. BTW.... That´s how i do pushups.... :D :D :D so funny
is there a way to apply this in a Table Matrix?
It's Great A V..
But I've requirement as need to find out recent 10 calls received by agent
Hello Avi, thank you for the informative tutorial on ranking... is there a way to work around a tie in rank by subranking the tied elements based on a secondary metric rather than specifying Dense or Skip in the RANKX formula?
+Kenny Lim Great question, I was thinking using FIRSTNONBLANK() but i don't think that would fully solve that. Let me think about it. Maybe bring it up in one of the live sessions and we can explore your specific scenario there.
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.TH-cam.com/PowerBIPro/Live **
@@PowerBIPro 5 years on, I now need this question answered. I have duplicate values that I want made sequential. Was 5 years enough time to think about it? haha
Hi Avi, You werer actually grouping on Group column but taking Brand column in function..Didn't got that? How its works actually
You are just amazing !!
Hi Erick Chicas.
Thank you for the compliment!
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Hi Avi,
Could you also please let me know once I rank within the table, how do I extract the related values in other columns?
Hi ANIKET,
The questions seems a little bit involved. The best option to get help on this may be joining one of my LIVE Q&A Calls.
See 👉 www.learnpowerbi.com/talk#qa
But going by what you said, it seems you have already ranked items in a table.
e.g.
USA = 1
Canada = 2
Germany = 3
etc.
Then you are perhaps looking to find, What are the [Sales] for the Country Ranked #3.
That should be possible using CALCULATE, FILTER and RANKX Measure.
But I would not be able to dive into it here. We can work through on the LIVE Q&A Call if you join.
We hold that call, once each month.
Otherwise, for immediate & private help with Power BI Consulting or Training, you can:
👉 Contact us for at www.learnpowerbi.com/contact
👉 Or by going to our website www.learnpowerbi.com and clicking the "Contact Me" Button
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
@@PowerBIPro Hi Avi,
Will do that but there are lots of people and not sure whether I will get chance or not
Is that works in the Card visual
"The value for ABC cannot be determined. Either the column doesn't exist or there is no current row for this column"
Trying to get rid off from this error from last 24 hrs.
Error came while performing Rank function. Please help @Avi Singh
It is not working,if I added new filters to report, any hint please
Merci !!!
Thank you for your feedback. If you'd like to learn more about Power BI , here is a link about our structured course on this Webinar
👉 web.learnpowerbi.com/waitlist-invite/
^RM
Excellent video thanks Avi !
Kevin! Thanks so much for your kind words. I am humbled and happy to be in a position to help others. This is the biggest joy anyone can experience. Power On! / Avi
Join me LIVE for #TalkPowerBI Fridays and ask your Power BI questions th-cam.com/users/powerbiprolive
i want to rank ASC not DES
But make Rank start from 2 not 1
could you help me ?
Hi Avi. How about breaking ties?
thank you so much, it is miracle
Thank you
Hi Jason Woodward.
You're welcome!
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: th-cam.com/video/AGrl-H87pRU/w-d-xo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays th-cam.com/users/PowerBIProlive ★
Is ranking always on a measure?
great video... Avi
never work
hello, i want ro rank transaction dates as below based on invoices belonging to the each customer, how do i go about it.
customer id invoice id transaction date RANKING
A 1234 10/09/2022 1
A 67585 09/09/2022 2
B 7585 24/10/2022 1
B 75805 26/10/2022 2
C 489055 05/02/2022 - 1
D 4855 06/02/2022 1