DYNAMIC TOP/BOTTOM RANKING using PARAMETERS // Beginners Guide to Power BI in 2023

in this video we're going to go through how you can show your top end or bottom end values within your reports using ranking I'm going to show you how you can hook these up into parameters so you can choose between seeing top or the bottom that is ranked being able to select how many items you want to see within your rank and I'm also going to show you how you can give your users the ability to choose what's being categorized within your ranking all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel where we cover tips tricks and best practices when working with power bi I upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so this video is building on top of a previous video that I created before where we covered how you can create a dynamic top end values so that you can see your total sales by categories from ranked by top three or top five now with this additional feature field parameters it gives us a lot of possibilities when it comes to being able to dynamically change how we're wrong ranking our products and the solution that I came up with today is a little bit complicated and a bit convoluted actually but we're gonna break it down into step by step so you can follow along as well so before we start let's get acquainted with the data sets that we're working with today which is the Northwind data sets it's for a fictional company called Northwind that sells grocery Goods internationally we have some data here like different orders for various products how much they're ordered how many as well as the total sales for each of these orders we have some order details like when they were ordered the product names for each of these orders and how these products are categorized by we also have a calendar table here for our time intelligence purposes and we also have a few pre-created measures that calculates the total sales as well as counting the number of orders or the quantity of our orders I've already created a relationship across all of these different tables but we're not going to focus too much on a relationship we just need to be aware that there is a relationship amongst all of these so if we go back to the report view here and let's start by bringing in our categories into a table and then we'll bring in total sales and if I select total sales here to show our values descending you'll see that now this table gives us a ranking of our categories by total sales being the the one with the highest sales will be at the top and then the lowest at the bottom now in the previous video we created some ranking to be able to only see the top X values in our table so we're going to start by recreating what we've done on that video so we're gonna go click and create new measure I'm going to name this one ranking and we're going to start by using this iterator function rank X wrap the table with an all and we'll use categories here comma the expression which is what we want to rank in this ranking which will be just simply the total sales value which simply ignore so another comma and then here we want to sort this ranking by descending which basically means the highest will be number one and then so on and so on so we'll close this and we'll bring this ranking measure into our table here so you can understand what it does so you'll see that using this ranking is basically the same as ranking it by total sales here which gives us one being the highest category two three all the way to eight now this ranking is what we use to determine how many items we're showing in our table so for example so let's modify our measure here and let's add a bit more detail here so let's say uh I'm going to just name this one ranking maybe rank visible doesn't really matter and then we're gonna use return here and we're going to add an if statement to say if are rank visible is less than or equals to let's say five then we won't want to simply just show the total sales of it and then we're going to skip the false which just returns nothing so what it will do I'm just going to convert this into pounds so what it will do now is it will simply give us the values for those that are in the top five you'll see that condiments is ranked at six so it gets ignored which now if you remove total cells from this basically just removes that category altogether or not removing it but making it hidden because there is no results in our ranking now if you change the value from five to three that will allow you to change how many items are being shown in the table now even further from that previous video we also hooked up this static number number three here into a numeric Fields parameter which allows your users to select and choose how many are being shown in our table or in your visual so in this case we're going to go to modeling new parameter numeric range we're going to name this one our ranking option and we'll say whole number start from 1 to 20 increments of one and then we'll just leave this ticked hit create and that will give us the ability to have a slicer here that we can drag which isn't really hooked up yet so if we go back to our ranking measure here we'll change this 3 into the selected this should be a measure here that was created with that ranking option value so now if you hit that and as you drag this slider it will give you fewer and fewer items or more depending on what your users select here in our slider pretty easy right foreign to controlling how we're ranking this and what we're showing so at the moment by default we're only able to see what is ranked at the top but not the bottoms and I mean if you look at the ranking measure that we've just created the only difference of how we get the button is by simply changing this order here instead of descending is ascending so we need to do two things we need to be able to dynamically change how our ranking by category is being ranked by either ascending or descending and then we need to give our users the ability to use the buttons to be able to toggle which one they want to see top or bottom so let's start with the latter which is a pretty easy one to do so we're going to go to modeling and add a new table we're going to Simply create here we're going to call it top bottom to keep it simple and I'm going to use curly brackets to create just some values in this table so within the double quotations I'm going to create one top and then with a comma I'm going to create a bottom so if I hit enter just to show you how that looks like it simply creates a one column table with just two values here top and bottom very very simple and the only reason why we're doing that is so that we can have the ability to slice and choose top or bottom here as a selection I'm gonna sort this also by descending so we have top there which at the moment is not really hooked up with anything yet so we're gonna create and check what is selected here in this value so that we can choose how we're ranking our categories by so we're going to go back to our measure here we're gonna create and I'm gonna rename this one into uh top category and then I'm gonna copy the measure that we have created the rank X I'm gonna name another one create a new variable I'm going to name it bottom category and then in this one we're going to change the sort order into ascending and then finally I'm going to create a new variable I'm going to call this ranking and I'm gonna create an if statement here to check what is being selected in our new top button table right so if we say if a selected value in the top bottom value is equals to top so if top is selected we're gonna use the the sort order on the top category variable else we'll use the bottom category variable so this should be pretty easy so now we have the ranking I'm just replacing it in the if statement so now what it does is it first tries to understand what is selected in our top bottom value if it stop use the descending order so that we have the highest values in the top and then if it's not use and show the lowest value at the top so if you hit enter you'll see what I mean so as you change so you'll see that it will still work it will still give us the values here and as we change that to bottom you'll see that it now gives us the bottom lists of those categories so what are ranked to the lowest and again with that it allows us to dynamically also change how many of those bottom ranking categories we can see so the last thing that I want to show you is the ability to change what is being ranked here in our visuals so for example at the moment we're only just looking at being able to rank the sales by our categories but what if you wanted to see the ranking by let's say products not just replacing categories in the products but being able to select and choose which one you want to see in the ranking how do we do that now for that it's a little bit complicated but I promise you if you follow what I'm doing you will be able to do it for yourself as well so the first thing that we'll need to do is we will need to use field parameters so that we can switch between categories and products and then with that field parameters we will use it as a way to determine if the selection is a category or product so that we can switch what we're ranking here in our measure so instead of categories we will be ranking products so let's go through it step by step and let me show you how how to do it so under modeling we'll hit new parameter and then here we'll select fields then we'll name this one we'll just name it breakdown and then we'll bring in category and product name because we want to be able to select between these two I'm just going to remove that the names the front there just so that the slicer it doesn't affect your data model it's just how it's shown on the slicer we'll hit create here then now you have the breakdown so that you're able to choose between each of these breakdown category or product now I'm going to select this value here at the top I'm gonna click format painter just so that we don't waste time so it just makes sure that this slicer is a single select slicer so now what we want to do is go to our table or whatever it is it's your probably a visual we'll remove the category name in The Columns and instead of category name we'll use the breakdown which is the which is the field parameter that we've just used now with category it still works as you can see everything still works but if you change it now to products it won't really quite work so we're gonna have to change or modify our measure for that which is the ranking one so the first thing that we'll need to do is we're going to create a duplicate of all these variables now I'm sure there is a better more efficient way to do this but I'll try different ways and it didn't work so I'm resorting to this because it works I'm going to show you and what we're going to do is going to convert these two variables to calculate and rank by products so simply replace any mention of the categories replace it with products and then under ranking here we're gonna just make it a bit tighter just have them all in one line because we're gonna need to have two of that and we're gonna have an if statement within an if statement so if we create the first if statement here and if we say if the selected value in our breakdown fields and it's important that you use Fields here contains string we're going to wrap it into contain string so what we're looking for is what is selected in our breakdown Fields so if it contains category then we want to use this if statement to sort or Rank by top or bottom category if it's not then we want to calculate the ranking by product because that's the only other option that we have so we're going to use top products here and just change one to bottom products so that should be everything that we need to do so now here we go so you can see that now with these selections you're able to choose between different breakdowns by category or by products it allows you to see the top and bottom ranking sales as well as control how many items you are seeing in your table or visual which is pretty handy and that's really it for this video I hope you now know how easy it is to implement Dynamic rankings in your power B reports and how you can utilize field parameters in order to make and add even more Dynamic elements within it thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you really liked the video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 19,572

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi topn, power bi bottomn, power bi ranking, power bi field parameters, power bi top bottom, power bi parameters

Id: jsVnMsR4x9U

Length: 15min 37sec (937 seconds)

Published: Wed Mar 22 2023