***** Related Links ***** Creating Dynamic Ranking Tables Using RANKX In Power BI - blog.enterprisedna.co/2018/09/18/dynamic-ranking-tables-in-power-bi/ Creating Multi Threaded Dynamic Visuals - Advanced Power BI Technique - blog.enterprisedna.co/2018/12/19/multi-threaded-dynamic-visuals-in-power-bi-advanced-power-bi-dax-techniques/ The Ultimate Advanced Viz. Technique in Power BI - Multi Measure Dynamic Visuals - blog.enterprisedna.co/2018/07/11/how-to-create-multi-measure-dynamic-visuals-data-viz-technique-in-power-bi/ ***** Related Course Modules ***** Data Visualization Tips - portal.enterprisedna.co/p/data-visualization-tips Advanced DAX Combinations - portal.enterprisedna.co/p/advanced-dax-combinations Financial Reporting w/ Power BI - portal.enterprisedna.co/p/financial-reporting-power-bi ***** Related Support Forum Posts ***** Dynamic Visuals - Number Formatting - forum.enterprisedna.co/t/dynamic-visuals-number-formatting/397 Passing a visual-level filter via a dynamic measure - forum.enterprisedna.co/t/passing-a-visual-level-filter-via-a-dynamic-measure/99 Dynamically Change X-Axis (Yearly and Quarterly) within a Visual via Slicer - forum.enterprisedna.co/t/dynamically-change-x-axis-yearly-and-quarterly-within-a-visual-via-slicer/1554 For more dynamic visuals support queries to review see here - forum.enterprisedna.co/search?q=dynamic%20visual
Hi Usman Bin Ihsan, Glad that our videos are of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hi Cesar, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hello Andrew Raad, thank you for posting your query onto our channel. We're providing a link below of the video where one of our expert had already created a video onto his TH-cam 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 TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ th-cam.com/video/__V6KYS5lyM/w-d-xo.html th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Mr. Sam. Great example. I have been looking for the RANKX function to prepare a report like this: get the topn values for all vendors across international locations in many currencies. Thanks
Hello William, Thank you for posting your query onto our channel. Well, you can also group these customers using the "Grouping and Segmentation" technique in Power BI. We're providing a few links of the videos from our 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. Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Cheers, Enterprise DNA th-cam.com/video/iH3gW_vy69o/w-d-xo.html th-cam.com/video/3BlTXhhbOro/w-d-xo.html th-cam.com/video/QhUBmO14u0o/w-d-xo.html th-cam.com/video/djLX6IUWVwY/w-d-xo.html th-cam.com/video/jLloQ_1KiGI/w-d-xo.html th-cam.com/video/DxQX6K8L6hU/w-d-xo.html
Hi! In a scenario, a product and category both are in same table. And I want to filter product by each category separately. i.e. products split into two slicers named bikes products and components products. When I filter each product, it should add to crosstab. Can you please help me how to achieve this?
Hi Bishnu, Sure you can, it depends on your exact requirements. But you could easily create custom groups based on logic and then use these to filter and return what you require. Be sure to checkout the videos below which should help you implement what you are after: th-cam.com/video/iH3gW_vy69o/w-d-xo.html th-cam.com/video/O66IWS7CQOs/w-d-xo.html If you have any specific queries be sure to checkout the Enterprise DNA forum. forum.enterprisedna.co/ Enterprise DNA
Sam - Great Video for sure! To take it a step further... How would you have your Top 10 Customers, then lump everything under the top 10 into "Other" so you could see what % of total Profits your top 10 makes up?
Thank you for the reply. Are you referring to the "Name by Rank" formula that you created? I created the same formula in my data and it just shows as blank. Not really sure what I did wrong.
Thanks for this ! amazing! If I want to show in the green graphic the others customer (besides the top 10) and then summarize it as a total, What I need to change in the measure ? thanks again.
Great tutorial, thanks, I like in very much, but I have one question, is it possible to use these technics also for two colums ? I tried to use it for total sales and total cost as 2 colums, but i can not figure out to get the correct total sum, do you have an idea . thanks for every tip
Hi Sam, love your work mate. Correct me if I'm wrong, don't we need to add IF CROSSFILTERED logic as well because when we filter by product, the customer table is not directly filtered but rather cross filtered? Thanks John
Have had a quick look...Don't think so in this case. The natural filtering just makes this all work. The ISFILTERED is all we need because where only ranking customers, not products in this particular case.
Hello, this is really cool if we want to create a visual table. But I struggle the way how I can put the ranking on the X axis of a visual graph (making kind of bins with customers between 1 and 10, from 11 to 20, etc). I would like to easily understand through the graph the top N customers who are doing the most of the turnover. So: Y axis -> cumulative turnover, X -> the bins. I tried to create a calculated table selecting my customer ID, the total turnover and the RANKX measure but the system returns errors. Do you have advises? Thanks in advance.
HI Mr. Sam; Is it possible to add a slicer with your top 3 customers, 5 customers, 7 customers 10 customers , 15 customers. How would you do that? Thanks
Thanks great work. What is the difference if I use the Customernames Column instead of the Customernames Table in the "ALL" Function? Results are the same.
In this particular instance there would be no difference. BUT depending on how you setup your dashboard there can be a big difference. An really good understanding of CONTEXT is important regarding this.
Hey, great video! I was wondering, is it possible to show your 10 worst customers instead of your best customers? I changed the measures a little bit, and it worked, till the last part where you have the use the function TOPN. PBI calculates the best customers, even if you try TOPN(1000-990; etc. (the dataset contains 1000 customers). Do you have a solution for this?
I have been unable to do a COUNT of the filtered table to see how many fulfill a specific criteria. for example how many customers in this ranking have sales above 1.2 million. How does that work?
Hi Sam, Thanks a lot for these advance techniques. I applied this formula on my data. But when I ranked my revenue, close number are ranked by same number. For instance, 13565 & 13239 & 13001 are ranked by 1 and etc. How can I solve this problem? I'w glad if you help me..
Make sure you have the correct virtual table inside your RANKX function. By the sounds of it this is not the case. My guess is this is the problem. Check out this video to learn more about RANKX - th-cam.com/video/W5lzIJ4qY7M/w-d-xo.html
Hello Bernardo, Thank You for posting your query onto our channel. We're providing a link of a video which was created by one of our Enterprise DNA expert over his TH-cam channel. 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 TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html th-cam.com/video/__V6KYS5lyM/w-d-xo.html
Instead of creating all additional measures.. What if I apply topn visual level filter. I don't know what are it's cons, I usually apply visual filter for top 10.
Sure you can do this in some cases. I like to use DAX where ever possible. Most advanced analytical insights with Power BI come with applying DAX so I stick to it all the time.
Thank you for sharing this great video. Sam, I have one question for you. How do you suggest me to build a table which ranks the top 10 clients by sales of a sales table, and also show a final single row in the table which group all the sales of all the "others clients" not included in the top 10 listed rows above? The sum of the last single row will probably be higher than more than many of the ranked top 10 clients. Thanks a lot!
Check out this post for the Enterprise DNA Support Forum. I explain how to do this here - forum.enterprisedna.co/t/showing-top-20-customers/126/3 I will look to create a video on this in the future. I've seen this question a few times now
Thank you for sharing this. It is very useful. I got stuck when I followed your video. I did the following steps and got an error in step3. Could you help me get around it? Step 1: _TOTAL SALES = SUM('data (2)'[sales]) Step 2: _SalesRank = RANKX(ALL('data (2)'[Product]),[_TOTAL SALES],,0) Step 3: _Top 5 Product Sales = IF([_SalesRank]
Formula looks fine to me. All I can think of is that there is too many brackets maybe in the final formula. To test break out each formula separately in a table with Product Name as the initial context. It should become clear where the issue is.
***** Related Links *****
Creating Dynamic Ranking Tables Using RANKX In Power BI - blog.enterprisedna.co/2018/09/18/dynamic-ranking-tables-in-power-bi/
Creating Multi Threaded Dynamic Visuals - Advanced Power BI Technique - blog.enterprisedna.co/2018/12/19/multi-threaded-dynamic-visuals-in-power-bi-advanced-power-bi-dax-techniques/
The Ultimate Advanced Viz. Technique in Power BI - Multi Measure Dynamic Visuals - blog.enterprisedna.co/2018/07/11/how-to-create-multi-measure-dynamic-visuals-data-viz-technique-in-power-bi/
***** Related Course Modules *****
Data Visualization Tips - portal.enterprisedna.co/p/data-visualization-tips
Advanced DAX Combinations - portal.enterprisedna.co/p/advanced-dax-combinations
Financial Reporting w/ Power BI - portal.enterprisedna.co/p/financial-reporting-power-bi
***** Related Support Forum Posts *****
Dynamic Visuals - Number Formatting - forum.enterprisedna.co/t/dynamic-visuals-number-formatting/397
Passing a visual-level filter via a dynamic measure - forum.enterprisedna.co/t/passing-a-visual-level-filter-via-a-dynamic-measure/99
Dynamically Change X-Axis (Yearly and Quarterly) within a Visual via Slicer - forum.enterprisedna.co/t/dynamically-change-x-axis-yearly-and-quarterly-within-a-visual-via-slicer/1554
For more dynamic visuals support queries to review see here - forum.enterprisedna.co/search?q=dynamic%20visual
The way its explained is in the best way possible. Your lectures are really adding value to my skills. Thanks once again and keep up the good work.
Hi Usman Bin Ihsan, Glad that our videos are of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Amazing!!!! Best Regards from Mexico.
Hi Cesar, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Thanks for the video.. Learnt 2 new DAX functions today. Appreciate the videos being so concise.
Really Great video! Thank you for sharing, is there anyway to group all the other sales in a group call "Others"?
Hello Andrew Raad,
thank you for posting your query onto our channel.
We're providing a link below of the video where one of our expert had already created a video onto his TH-cam 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 TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!!!
forum.enterprisedna.co/
th-cam.com/video/__V6KYS5lyM/w-d-xo.html
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Mr. Sam. Great example. I have been looking for the RANKX function to prepare a report like this: get the topn values for all vendors across international locations in many currencies. Thanks
This was exactly what I was looking for you legend!!!!
Hi! I loved this video. Just wondering if there was a way to group up all the other customers instead of having them as blank?
Hello William,
Thank you for posting your query onto our channel.
Well, you can also group these customers using the "Grouping and Segmentation" technique in Power BI. We're providing a few links of the videos from our 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.
Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI.
Cheers,
Enterprise DNA
th-cam.com/video/iH3gW_vy69o/w-d-xo.html
th-cam.com/video/3BlTXhhbOro/w-d-xo.html
th-cam.com/video/QhUBmO14u0o/w-d-xo.html
th-cam.com/video/djLX6IUWVwY/w-d-xo.html
th-cam.com/video/jLloQ_1KiGI/w-d-xo.html
th-cam.com/video/DxQX6K8L6hU/w-d-xo.html
Hi! In a scenario, a product and category both are in same table. And I want to filter product by each category separately. i.e. products split into two slicers named bikes products and components products. When I filter each product, it should add to crosstab. Can you please help me how to achieve this?
Thank you very much Sir for this video... Can we filter a data by top 10,20,50,100 like this using a slicer.
Hi Bishnu,
Sure you can, it depends on your exact requirements. But you could easily create custom groups based on logic and then use these to filter and return what you require.
Be sure to checkout the videos below which should help you implement what you are after:
th-cam.com/video/iH3gW_vy69o/w-d-xo.html
th-cam.com/video/O66IWS7CQOs/w-d-xo.html
If you have any specific queries be sure to checkout the Enterprise DNA forum.
forum.enterprisedna.co/
Enterprise DNA
This is so helpful. Is there a video that can also show your top 10 clients as a percentage of total revenue?
Hi Andrea Meade, we have an available video tutorial on this topic. You can check the video here:
th-cam.com/video/ljAINOUt-X8/w-d-xo.html
Sam - Great Video for sure! To take it a step further... How would you have your Top 10 Customers, then lump everything under the top 10 into "Other" so you could see what % of total Profits your top 10 makes up?
I explain how to do this here - forum.enterprisedna.co/t/showing-top-20-customers/126/5
Thank you for the reply. Are you referring to the "Name by Rank" formula that you created? I created the same formula in my data and it just shows as blank. Not really sure what I did wrong.
Hi Sam, like Jazzista 1967 is there a video combining this ranking with a top 5, 10, 50 etc. slicer?
I think this is what you're after - th-cam.com/video/S7eMccO1_bE/w-d-xo.html
Thanks for this ! amazing! If I want to show in the green graphic the others customer (besides the top 10) and then summarize it as a total, What I need to change in the measure ? thanks again.
Hi wellington, see here for some ideas on this - forum.enterprisedna.co/search?q=RANKING%20Customers
Very Great techniques, thank you Very much.
Great tutorial, thanks, I like in very much, but I have one question, is it possible to use these technics also for two colums ? I tried to use it for total sales and total cost as 2 colums, but i can not figure out to get the correct total sum, do you have an idea . thanks for every tip
Good one. Thanks
Please help! How to rank rows in terms of transaction date?
How do you rank a column with multiple slicers and filters?
Should be able to do that in a similar way to the video. Can't imagine why it would be different
Hi Sam, love your work mate.
Correct me if I'm wrong, don't we need to add IF CROSSFILTERED logic as well because when we filter by product, the customer table is not directly filtered but rather cross filtered?
Thanks
John
Have had a quick look...Don't think so in this case. The natural filtering just makes this all work. The ISFILTERED is all we need because where only ranking customers, not products in this particular case.
Great video, thanks
Hello, this is really cool if we want to create a visual table. But I struggle the way how I can put the ranking on the X axis of a visual graph (making kind of bins with customers between 1 and 10, from 11 to 20, etc). I would like to easily understand through the graph the top N customers who are doing the most of the turnover. So: Y axis -> cumulative turnover, X -> the bins. I tried to create a calculated table selecting my customer ID, the total turnover and the RANKX measure but the system returns errors. Do you have advises? Thanks in advance.
Hi Mr.Ferraglia, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=RANKX
HI Mr. Sam; Is it possible to add a slicer with your top 3 customers, 5 customers, 7 customers 10 customers , 15 customers. How would you do that? Thanks
I'll place that on the list for videos to complete. You need to use the dynamic visuals technique.
Thanks
Thanks great work. What is the difference if I use the Customernames Column instead of the Customernames Table in the "ALL" Function? Results are the same.
In this particular instance there would be no difference. BUT depending on how you setup your dashboard there can be a big difference. An really good understanding of CONTEXT is important regarding this.
Hey, great video! I was wondering, is it possible to show your 10 worst customers instead of your best customers? I changed the measures a little bit, and it worked, till the last part where you have the use the function TOPN. PBI calculates the best customers, even if you try TOPN(1000-990; etc. (the dataset contains 1000 customers). Do you have a solution for this?
SuitUp! Not sure if you still need this but sort desc in rank measure will get you 10 “worst”.
@@stevenfoster5799 I did figure that out in the meantime. But thanks anyways
this is amazing!
hi dear
how do you make the key measures table putting all measures together?
I run through this in both Ultimate Beginners Guide courses which are free at portal.enterprisedna.co. Check these out if you haven't already. Chrs
I have been unable to do a COUNT of the filtered table to see how many fulfill a specific criteria. for example how many customers in this ranking have sales above 1.2 million. How does that work?
Hi Ben, for help like this check out the Enterprise DNA Support Forum for some ideas on this -
forum.enterprisedna.co/search?q=RANKING%20COUNT
Thank you
Hi Sam, Thanks a lot for these advance techniques. I applied this formula on my data. But when I ranked my revenue, close number are ranked by same number. For instance, 13565 & 13239 & 13001 are ranked by 1 and etc. How can I solve this problem? I'w glad if you help me..
Make sure you have the correct virtual table inside your RANKX function. By the sounds of it this is not the case. My guess is this is the problem. Check out this video to learn more about RANKX - th-cam.com/video/W5lzIJ4qY7M/w-d-xo.html
How can you create a dynamic "all other" of what is not part of the top10?
Hello Bernardo,
Thank You for posting your query onto our channel.
We're providing a link of a video which was created by one of our Enterprise DNA expert over his TH-cam channel.
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 TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
th-cam.com/video/__V6KYS5lyM/w-d-xo.html
Instead of creating all additional measures.. What if I apply topn visual level filter.
I don't know what are it's cons, I usually apply visual filter for top 10.
Sure you can do this in some cases. I like to use DAX where ever possible. Most advanced analytical insights with Power BI come with applying DAX so I stick to it all the time.
Can we use this function if SSAS is the data source?
With SSAS you would need to complete the calculations on that side rather than in Power BI
Thank you for sharing this great video.
Sam, I have one question for you. How do you suggest me to build a table which ranks the top 10 clients by sales of a sales table, and also show a final single row in the table which group all the sales of all the "others clients" not included in the top 10 listed rows above? The sum of the last single row will probably be higher than more than many of the ranked top 10 clients. Thanks a lot!
Check out this post for the Enterprise DNA Support Forum. I explain how to do this here - forum.enterprisedna.co/t/showing-top-20-customers/126/3
I will look to create a video on this in the future. I've seen this question a few times now
Very Cool :)
It's not working for me, keeps telling me "argument 3 in all functions required, can anyone help?
Just double check all aspects of your formula. The error suggests that one of the formulas is not written exactly as it should be
Great tutorial, thanks
Thank you for sharing this. It is very useful. I got stuck when I followed your video. I did the following steps and got an error in step3. Could you help me get around it?
Step 1: _TOTAL SALES = SUM('data (2)'[sales])
Step 2: _SalesRank = RANKX(ALL('data (2)'[Product]),[_TOTAL SALES],,0)
Step 3: _Top 5 Product Sales = IF([_SalesRank]
Formula looks fine to me. All I can think of is that there is too many brackets maybe in the final formula. To test break out each formula separately in a table with Product Name as the initial context. It should become clear where the issue is.
i want this dataset
please dataset name
See here for some ideas on this - forum.enterprisedna.co/search?q=Data%20set
I am using ALL But still i am getting as 1 to each row ....