So I'm going to start building it here and hey As always you can go to learnpowerbi.com/download to get the finished file and try these out for yourself. So let's start here where all I have is a simple table with the sales measure on there now this is a pretty simple measure just a Sum of sales amount as you can see over there and you can see that right now. I have this in my.
In my table over here, and it's not really showing top 10 It matches the overall total sales that I have for the selected here which in this case is 2016 so let's add some more meat onto this and what we're gonna do is let's go ahead to the customer table and Bring in the full name off the customer So we're gonna have full name and I'm gonna use these later but I'm gonna add them, right?.
Now the order count and the customer count and you'll see where we will use it later on So I've added those measures but again right now we're seeing all the customers So let's get started by filtering it down to the top 10 in our case So I have this one selected and in here all the fields shown here are also available to me over here now I could add a top and filter to the full name.
But in this data set, we have two different customers with the same name Which is possible now. Well, hence, what we're gonna do is in fact filter down to the customer key now It's not perfect solution, but gets us close enough. It works for us in this scenario So let's say I'm gonna add the customer key So again, it's a it is not being shown in the table.
You would add it as a page level or report level filter but for us and this is Exactly what we want this So now let's go to the customer key and in here if you click on the drop down to advanced filtering You would see the top end option right there.
So we're gonna select top end and then you do have options for like top or bottom So we're gonna say top and then it says well how many in this case we have 10 elements now Of course if you just want to focus on your top 5 customers or something like that You can set that here now often. I hit enter and I expect something to happen and nothing changes in fact.
The apply filter button doesn't even light up because there's one field which is still Added which is top 10 by what now? Often when you're showing just one field on the on there like sales now I did I did add other fields, but you might just have sales there.
So to you, it might be obvious that hey I want top 10 by sales But it's not obvious to power bi so we're gonna help it out and say yep, you know Show me the top 10 customer buy sales, so I'm gonna drag that value over here And again, sometimes I expect something to happen, but you still have to click the apply filter button down at the bottom So I'm going to click apply filter and notice what happens over here.
So now you can see we're only seeing the top 10 customer buys sales now You don't obviously you can change it so you can say hey I don't care about the customer sales Instead I would want to see the top 10 buy order counts So I I drag that over here and now when I apply filter it's now By top ten customers by order count now.
Posts Related:
- Excel Today - PowerPoint Morph with Joe Office
- DAX for Power BI Part 2.5 - Text in Calculated Columns
- How To Add Push Notifications For A Blogger Website
Yeah
You you might notice that it's actually showing me more than ten elements and that because off there are ties here We'll talk about ties more in this video as well One other thing that I would emphasize here is that I can I don't have to show The measure that I'm using to calculate the top ten so I can say show me the top ten customers.By their order count but not show that value in there at all So I I just want to say hey show me the top ten customers By order count and show me who they are and what their sales numbers are But order count is only being used for filtering now you can do that But I almost never.
Do that because it ends up being a little confusing. So usually whatever measure I'm showing there That's the one I use to to use the top end filtering so great So now we can see our overall numbers for the complete year from all the customers But we can also keep a close eye on our top are besties our favorite customers our top ten customers.
See how much they're contributing if you wanted to you could obviously calculate this as a percentage of total sales what percentage of our sales is coming from top ten customers and It has the magic of power bi which is you can slice and dice it any which way you want so this is a worldwide customer, but you can say oh, I only want to see the top ten customers in US or Australia or you know how to Kingdom on France and you get the idea.
But now let's try to see it by month and you're gonna see something we had hit on earlier So if I go on January then I see oh, these are the top ten customers in January. But watch what happens in February It's showing us eleven customers and he had seen the ties when we were playing around with order count as well so you can see it at the Bottom we have a tie at the bottom a three-way tie it seems and that's why it's showing us not ten.
But actually eleven customers and even the total is Not for the top ten because it just stopped down when there is a tie and includes all of them It is showing counting up all of these So watch out for that ties can be a challenge when you're trying to do top-end or top-ten filter.
Another challenge is if you want to see some kind of a trend of contribution or just look at this contribution by top ten and just see it over time or Across country or some other attribute now for both of these handling the tie scenario and showing trends the top-end Dax function can come in handy so you can get Download this file and examine the measure and how that's been built.
But I just wanted to show you the end result and compare the two. So right now they look alike so this is a Dax measure so sales, it's showing me the sales of top ten customers and I also You know use this and and divided this by the total sales we had to calculate the percentage sales from top ten customers Right, so that one the percentage sales is I simply just give me the sales top ten and say let's just divide 20.6% right? So but the one of the first of all the power of these measures, is that now you have this number.
You don't have to go about setting a visual level filter every single time You can just use that to slice and dice that so you can see. Okay, cool Well, what about the sales top top ten customers by country and you can see well Australia Canada France Germany how much in.
Absolute sales dollars is
Coming from top ten customers in each of those countries, right? How top dependent have a top-heavy re and just to make it make it kind of apples to apples because United States Would well does have a really big chunk of the sales in in by country and.You know, we we kind of see that in this United States is a big block. It's fair to calculate that as a percentage so here you can see that France is the most stop having 6.2 percent of their sales is coming from top ten customers and United States It's really really small point six percent and not just that we can also trend it over time and see how things have changed So you can see that we started the year kind of a little top-heavy morale for sales revenue coming from the top ten customers.
But then it has trended down. So that is interesting And of course, you have the magic of power bi where you can slice and Dicers show me the trend for France Show me the trend for Germany and you can see France Trended down and let's see United States and that really trended down right so you can always slice and dice that so that's one big advantage of using measures but let's go back to the challenge we had for theis now the scenario that has shown you was.
January was fine. It was showing us kind of 10 top 10 customers But as soon as we switch to February there was a tie at the bottom and that ended up showing us 11 customers But you can see here that our Doc's measure is calculating it correctly and you can do the math yourself if you like just you know take the total to 17 through 7 and.
Remove the last the 11th person to five-on-five and the answer would be what Dax is returning to you So you've seen two approaches do let me know in the comments below Which one do you prefer or tend to use and don't forget to download the file at learn power bi comm slash download Hey keep watching more videos and keep learning power bi but if you did enjoy this video I would love to hear from you. So leave a comment like subscribe.
