Ranking by Product in Power BI
What is Ranking?
Alright! I suppose this first question is the one that should be answered first. Ranking is the process of putting things in order. So in a competition the person with the most points would come first, the one with the second most points comes second, the third comes third an so on. So in relation to this exercise we are going to rank products.
In business you may want to clearly see what you're best selling product is, then you'll know what to concentrate on and even what products you might want to develop. Ranking is a technique that can make it clear to anyone looking at your data what those products are. Plus, ranking is a bridge to other data analysis formula's such as Running Totals (Cumulative Totals) or ABC Pareto analysis.
How do I do it
- First thing is to Download the Ranking Starter Exercise PBIX file (PBIX format is the native format for Power BI.)
- Once the file is downloaded open it up and have a quick look at the structure. As you can see there is a Date table along with a fact table which lists internet sales (FactInternetSales) along with a Product table (DimProduct).
- What we want to do is to Rank the Products from 1 Best selling to 1000 worst selling based on the total revenue.
- If you click on the Visualisations button we're ready to add a Matrix.
- Add a Matrix.
- From the DimProducts table add the EnglishProductName to the Rows data field Section.
- From the Key Measures Table (created earlier) drag TotalRev to the Values data field section.
- On the Matrix visualisation click on the TotalRev header to sort the column in descending order. Your matrix should look like:
- Now it's time to add the Ranking Measure, right click on KeyMeasures and select New Measure.
- Enter the following:
ProductRank = RANKX(ALL(DimProduct[EnglishProductName]),[TotalRev])
- Press enter when checked and finished.
- Drag the formula onto the matrix, you should now see:
- There you have it.
Ranking is just the start of many different.
Hope the above helps, of course there's so much more you can do with this. If you haven't already done so check out my Power BI tutorials videos. If you want to know more about sorting by month, weekday or even season then check out the sort by month step by step tutorial.