RANKX Considerations - Power BI & DAX Formula Concepts

In this example I showcase the key tips to understanding the DAX formula RANKX.
There are a number of concepts to understand well to make sure the results you expect are created.
I run through the most important concept which is 'CONTEXT'. Once you nail this then you can really understand how to use this formula effectively.
Good luck!
**** Video Details ****
0:43- Sample Basic RANKX Scenario
1:14 - Using The ALL Function Within RANKX DAX Calculation
3:01 - Sample Complex RANKX Scenario
4:37 - Using the CROSSJOIN Function
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - enterprisedna.c...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
Learn more about Enterprise DNA - www.enterprised...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Events - enterprisedna....
**** Related Links****
Using Dynamic Visuals On Ranking Based Parameters In Power BI - blog.enterpris...
Find Top Customers Using RANKX In Power BI - blog.enterpris...
Master Virtual Tables in Power BI Using DAX - blog.enterpris...
**** Related Course Modules****
DAX Formula Deep Dives - portal.enterpri...
Advanced DAX Combinations - portal.enterpri...
Mastering DAX Calculations - portal.enterpri...
**** Related Support Forum Posts****
Problem with Ranking (RankX), Showing all Customers - forum.enterpri...
RANKX Vs TOPN what is the difference? - forum.enterpri...
Dynamic RANKX with Other Category - forum.enterpri...
For more rankx support queries to review see here - forum.enterpri...

[@EnterpriseDNA]

***** Related Links***** Using Dynamic Visuals On Ranking Based Parameters In Power BI - blog.enterprisedna.co/make-your-visuals-dynamic-based-on-rankings-in-power-bi/ Find Top Customers Using RANKX In Power BI - blog.enterprisedna.co/detailed-ranking-example-using-dax-in-power-bi/ Master Virtual Tables in Power BI Using DAX - blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/ ***** Related Course Modules***** DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives Advanced DAX Combinations - portal.enterprisedna.co/p/advanced-dax-combinations Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations ***** Related Support Forum Posts***** Problem with Ranking (RankX), Showing all Customers - forum.enterprisedna.co/t/problem-with-ranking-rankx-showing-all-customers/299 RANKX Vs TOPN what is the difference? - forum.enterprisedna.co/t/cumulative-total-question/1054 Dynamic RANKX with Other Category - forum.enterprisedna.co/t/dynamic-rankx-with-other-category/4108 For more rankx support queries to review see here - forum.enterprisedna.co/search?q=rankx

[@benmatthews976]

I've spent the last 6 hours tearing my hair out with RankX! Crossjoin was what i needed. I love you.

[@EnterpriseDNA]

That's great, nice one.

[@jerrymigliaccio5137]

Super clear explanation, Thank you for helping me solve this problem!

[@cesarsaldana3429]

Hello Sam, I think the following measure can work the tables are related. RANKX( SUMMARIZE( ALL(Sales), Customer[customer name], Product[ Product name)), [Total Sales], , Desc)

[@ajkristy7026]

Thanks for the video. As a note, blanks in a sortable fact field will create the 1's condition. You can eliminate this by either eliminating blanks in M or using a calculated measure and the NOT(ISBLANK) condition.

[@BenEdwards-nr7ix]

Another great video, very clear and helpful, thanks so much

[@azizquazi]

Great movie to understand the changes in Context.

[@Media_Advertising_Analytics]

Thanks for the very helpful video. Would you have any guidance on utilizing a variable tied to a user-selected parameter (e.g. Top N or Top Bottom) that uses RANKX and FILTER (to filter out rows based on a column value of

[@EnterpriseDNA]

Hi Los Angeles, It's a bit difficult to assess and give recommendation without looking at the data structure, model and working of the PBIX file for the reference. You can reach out to us on our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our KZbin channel and LinkedIn group to keep posted on the latest data skills and tools updates. Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/

[@DiogoXerez]

Great video! Is there a way i can fix the skipping sequence of the ranking?

[@EnterpriseDNA]

Hello Diogo Xerez, Thank you for posting your query onto our channel. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA KZbin channel pertaining to this topic. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA blog.enterprisedna.co/rankx-in-power-bi-developing-custom-tiebreakers/ kzbin.info/www/bejne/fpy1Yn2Mr9KpiNk kzbin.info/www/bejne/Z5jMp6KpgrGbeas

[@xodrinker]

this is one smart way of doing this Sam, thanks for the explanation!

[@tomaskavicky]

cheers mate, finally worked for me ;)

[@EnterpriseDNA]

Hi Tomas, glad this video is of help. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@bmv777]

Thank you Sam. Useful like always. In my case if I add measure Product &Customers Ranking to a table visual containing products and customers and,after about 30 sec, ended with memory error . Maybe the problem is the huge number of row? I have 36K customers , 65k products and 3.2M rows in sales table.

[@EnterpriseDNA]

Yes that's a lot of rows to iterate through for RANKX. I think some optimization would have to occur with that formula. Maybe look at reducing the 'virtual table' part of the formula.

[@rideout8454]

Like the way you have explained!

[@RehanShaikh-ip9hh]

Hi thanks for your video. How can we remove the rank showing on total row in your table. Pls suggest

[@EnterpriseDNA]

Hello Rehan Shaikh, Thank you for posting your query onto our channel. Below are the measures/formulas provided for the reference wherein you can remove the "Rank" from the total row from your table. Product Rank = VAR _Product_Ranking = RANKX( ALL( Products ) , [Total Sales] , , DESC ) RETURN IF( ISBLANK( SELECTEDVALUE( Products[Product Name] ) ) , BLANK() , _Product_Ranking ) Customer Rank = VAR _Customer_Ranking = RANKX( ALL( Customers ) , [Total Sales] , , DESC ) RETURN IF( ISBLANK( SELECTEDVALUE( Customers[Customer Name] ) ) , BLANK() , _Customer_Ranking ) Also if you don't want to write a complex measure then in that case you can format it and hide the "Rank" from the total row. Below are the steps provided for the reference - Select the table. Click onto the "Format" menu. Go to the "Field Formatting" section. From the drop-down menu, select the "Product Rank" option. Select the font color as "White". Enable the "Apply to total" option and disable the "Apply to values" option. And you'll see that "Rank" from the total row will get "Hidden" i.e. it's still calculated but not shown whereas in the case of measure/formula it's simply calculated for all the individual rows but not for the total row. So we've provided you with both the options. You can go for either of them whichever suits your requirements. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Along with it you can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@giri41]

Hi Sam, How can we filter on region or state but still rank holds the original but it ranking depends on state we choose

[@mehdihammadi6145]

Thank you for sharing

[@hi_vishy]

Can we use allselected in place of all in this example?

[@shakeelahmad-vs6hm]

How to use this when you have survey data. I need help to report a survey question when respondents have 5 option to rank/preferences? So when you get responses data, there are 5 columns for each respondent and when you unpivot the data then how we work with that data? Can you please help me with this? Thanks

[@jasonmxx]

How to use rankx on dates? I wanted to filter my chart to show only the 10 most recent transactions, however I do not know how to convert the dates column into an expression. please help! thanks!

[@akashragi2347]

If I wanted to skip the ranking for blank category names , how can I do that ?

[@EnterpriseDNA]

Hello akash ragi, Thank You for posting your query. We're providing a link from our Community Forum where one of our experts have addressed this issue. This will help you in your scenario. Moreover, you can also download the PBIX file provided as a solution in that thread. The link is provided below: forum.enterprisedna.co/t/pareto-topn-tree-map-issue/8690/9 For more queries, you can also reach out to us through our Community Forum. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for.🙂 Also make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Along with it, you can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Happy Learning!!! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ forum.enterprisedna.co/t/pareto-topn-tree-map-issue/8690/9

[@pratikfutane8131]

Great video 🙂

[@gulhermepereira249]

Great explanation, Sam! What if I had a drill through page RANKX, I woldn´t be able to use ALL. Any recommendations?

[@EnterpriseDNA]

Hi Gulherme, Thank You for posting your query onto our channel. Could you please post your query onto our community forum where our members as well as our Experts will be able to assist you in a better manner? We're providing a link below of our forum. Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/

[@ecofriendlyify]

Hi Sam, I need to show the best ever number for a KPI (aggregated on a weekly basis), However In my case I have the same highest value appearing for different weeks. How can I simply pick the first highest value and show it as the best ever value for that KPI? Any help you could provide will be appreciated.

[@EnterpriseDNA]

Hello kartik kanodia, thank you for posting your query onto our channel. Well, you can try out the below formula pattern to show the highest value ever in your KPI card using "MAXX" function. Below is the formula pattern provided for the reference - Highest Sales of All Time = CALCULATE( MAXX( Sales , [Total Sales] ) , ALL( Dates[Date] ) ) We're presuming that you want to show the sales figure. Alternatively, using same formula pattern you can determine highest value of any category. Still if you've any questions, you can post it on our Power BI Community Forum where our members and expert team will be able to assist you in a better and efficient manner. We're providing a link below of our Community Forum as well. Hoping you find this useful and meets your requirements that you've been looking for.🙂 Happy Learning!!! forum.enterprisedna.co/

[@nehachak]

Thanks, Sam for another awesome video. When creating the rank measure if in place of value I'm giving another measure, the result is going incorrect {eg. sum(Column)}. However, if I'm including another step and creating a Measure and then using it as a value in the RANKX function, it's working fine. Please please explain why. What trick is Power BI playing at the backend? Thanks in advance.

[@EnterpriseDNA]

Hello Neha Chakraborty, thank you for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference since we don't know what context you're supplying and how the results are getting evaluated. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

[@Gledii]

Hi, i need to create a scoring sistem (5-1) and i have a measure that calculates the %of the total sales for specific products. some products show 30% of total sales, others 25% and several 2%. How can i create a rank associating the top % as 5, decreasing to 1? any idea? thanks a lot

[@Gledii]

Just rewatched twice you video and figured it out! great explanation!!! thanks

[@erikabauling5978]

Hi! thanks for this content, very insightful. What if I want to rank by person and time? I am working on a scorecard but all my values which I want to rank are measures from a larger data set. I appreciate your help.

[@EnterpriseDNA]

Hi Erika, It's not possible to suggest anything without looking at your data Model, Measure etc. If you are a member of Enterprise DNA, please raise a new topic at forum forum.enterprisedna.co/ or explore forum for similar issues. Regards, Enterprise DNA

[@dragontrans]

Ranking with Hierarchy Drill-Down ? :)

[@MrVargs120]

Great video! I have my rankx setup and working properly, however what I'm looking to do is keep the overall rank when slicing my data. In my example, I rank stores 1-54 based on Total Sales. What I want to do from there is slice by region, for example say the North Region. I slice by North Region and the rankings are then reordered based on only the stores in that region. If the North region contained overall store ranks of 1, 15 and 54 how would I go about getting 1, 15 and 54 to display instead of 1, 2, 3?

[@SyedUsman]

Instead of all use allselected

[@joanhauff2479]

Could you please advise the formula for the "Total Sales" measure?

[@EnterpriseDNA]

If you download this resources here you'll find a few combinations of how this can be calculated - enterprisedna.co/power-bi-resources

[@1yyymmmddd]

@@EnterpriseDNA Great video, as always. Thank you! But why it doesn't work if instead of [Total Sales] I put SUM( 'Sales Data'[Total Revenue] ) ?

[@giri41]

thanks you Sam. most of the times, I couldn't see my key figures in the formulas.can you help please. like RANKX(ALL(Column), sales (I can't see),,desc)is that because my source data is from HANA

[@EnterpriseDNA]

Hi Lakshmi, see here for some ideas on this - forum.enterprisedna.co/search?q=RANKX

[@jonnyparsons3217]

Hi Sam Great tutorial as always from EDNA. However, having replication the CROSSJOIN on the last example (see below), it kills the response time Statement and SKU Rank by Selection = RANKX( CROSSJOIN(ALL(Products[SKU]), ALL('Statement Addresses'[Account Name]) ), [Selection], ,DESC) Any advice on speeding this up would be appreciated? Thanks

[@EnterpriseDNA]

For help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=RANKX