Let me show you how you can use Power BI to rank items
within a group. We will create a ranking chart like this using the DAX Rank X function, and if you are new to my channel
make sure to hit subscribe and click the bell, so you are notified whenever I go live to answer your Power BI questions.
So this question was asked by Ankit in the Learn Power BI family and Ankit when he posted this question
He he he said that he had been able to create this using an R visual
But he wanted to be able to define that using Dax and no surprise because Dax has
Magical powers right Dax measures have magic and that's the magic of define once used everywhere.
Once you define the DAX measure no no matter if it's really simple one like some or something really really complicated
Once you define it, you can then slice and dice it and visualize it any which way you want
So, let's see how we can do this using Dax. So I recreated a table similar to what Ankit had provided. Now
this one is a very simple data set but it could easily have been a
more robust more
Complicated data set possibly with millions and millions of rows and what I'm going to show you is still gonna work for that data set
As well. So here I have a simple data set now
Matrix is a little bit complicated for me
so I'm gonna keep it really really simple and I want to keep it a simple list and in fact I've even
Filtered it down to a single grouping. So again
UNK it wanted things to be ranked within a specific group so you can't see the whole thing
But that says assortment and here you can see that we have the rank one two
Three and so forth. So that's what we're trying to do
so in here I'm gonna keep it really really simple and just filter down to assortment and then I have
Sorted it by total points as well. And then let's try to write a rank measure. So I'm gonna go to modeling
Measure and I'll say rank and of course we have the rank X measure and give it the table
We only have one table here brand rating and rank it by total points
Which is a really simple measure is just to sum. In fact, that's the one that's shown right here
so that's our measure and now let's add it to our
Column now, of course, it didn't work and that's because rank X the way it works is it's a filter function
It's an iterator all ex functions are iterator functions. They go row by row
So it's going to go row by row calculate total points and then rank those rows, but when its operating over here
There's actually only one row available to it
So if you rank the row against itself is always gonna be row row one, right? So that's why I just returning one
So to change that what we're gonna do is we'll say all brand ratings. So I'm gonna clear that filter
So now is running over all those drugs now a word of caution here that I have a very simple data set you
Probably would want to separate the brand and grouping and if you have something like this in a separate lookup table
so make sure your data kind of looks like this with a with the data tables down below and the lookup tables up top and
And then it that that probably ship would be best to use rank X, but for now, this is gonna work
So this kind of works but notice it says one two, and then skip a few and seven
Well, that's how I do push ups one two, skipped a few hundred and one
But yeah, so that's not quite what we had in mind for the assortment one eventually
We want to be able to lay it out just the way on get had in mind and rank it from one through eleven
So within that group, how can we do that?
and of course we
Understand what's happening here by simply clearing the grouping filter and now you can see that the rank is actually not
Working just on the resort man. It's just it's just a global ranking. Well, that makes sense because we had said
rank all
Brand ratings so all rows one by one. So we're gonna change that slightly and
Let's do another
measure here and I'm gonna copy that because I'm lazy and I'll say rank we didn't grow up but we're basically
ranking brand so rank
Let's say we didn't write and let's say brand
within the group so within that and here what we're gonna say is is we have our filter for grouping what we're gonna
Really use the value. So within that group you are ranking the brand
So we're gonna pass that column in so rank each brand against each other. That's what essentially we're trying to say here. So
That's our new measure and let's add it on there and let's filter it down to one of those and bingo
You can see assortment it just you know, so ignore this one
This was our first effort, but now it's ranking at 1 through 11. Let's try layout and
Perfect. Same thing here the sorted by total points and you can see that ranking is working within that
Would it work if we spread it out in the matrix format?
Of course, it would and I have that laid out over here and you can see that
Let's try that again
So for each each of these you have the ranking within that group that all the brands have been
Ranked based on their total points and I've added a little bit of conditional formatting to give it some color the way
The way on get had initially on his our chart
but again these this is based using a Dax measure which is magical with the magic of defined ones use everywhere if you have any
other rank X or Dax or power bi related questions post them in the comments below or
Join me live on talked Barbie on Fridays and I'll see you then power on my friend
Hey keep watching more videos and deep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends