Now we know Touring-1000 Yellow is to blame; but there are also other underperforming products (so to speak). The bikes Product Manager and the Sales Manager are very curious to know the 10 less profitable products in our shop, in order to review the catalog, and come up with a creative way to cover losses. You are tasked with the "10 most money-losing products of all time" report. How would you do it? Well, yes, sort them and print just the first 10 lines could be an easy fix; but there's a more elegant solution. Use the Rank function.
My opinion is that in BI there is not only one "single" solution to reporting requests. Everyone has his/her preferences, we can get to the same results from different path and, as long as the numbers are true, there is no wrong or right way of doing things. There may be different performance issues and different levels of complexity, but the message is: if you can provide the correct number one way or the other it's OK. Do as you feel more comfortable. In this recipe I will show one way to do this, surely it's not the only one, but it gives me the opportunity to show metrics of metrics (nested metrics).
- Go to Public Objects | Metrics and create a new empty one.
- When the Metric Editor appears, look at the big text area on the right named Definition, click on the little button with the f(x) label. This will bring up a list of functions.
- Open the Select a category dropdown and click on Rank and NTile. From the Select a function list, choose Rank and click on Next.
- In the Arguments window, click on ellipses (…) next to ValueList. In the dialog that opens, double-click on Public Objects, then Metrics, then Sum ProductMargin from FactResellerSales, and back to the Arguments windows click on Finish, the definition should look like this screen capture:
- Click on Validate to check it; if no error appears, click on Save and Close.
- Name the metric
Rank ProductMargin from FactResellerSales. - Now go to Public Objects | Filters, and create a new empty filter.
- In the Filter Editor, select the Public Objects shortcut, open the Metrics folder, and double-click on the one we just created.
- This time we want to filter just the first (top) 10 values of the metric: so modify the Operator option to Less Than or equal to and set Value to
10, click on OK. - Click on Save and Close the filter and name it
10 worst ProductMargins. - In My Personal Objects | My Reports folder, create a new blank report.
- Add the Product attribute, the Rank ProductMargin from FactResellerSales metric, and then add the Sum ProductMargin from FactResellerSales metric too.
- Click on Public Objects shortcut, enter the Filter folder and right-click on the 10 worst ProductMargins filter, from the context menu select Add to Report Filter. Go to Grid View, and sort on the first metric column.
- Now we have only the 10 less-profitable products and how much money we lost on them, sadly for the PM they're all bikes.
- Save the report as
10 Less profitable Products.
Here we have a Sum metric nested into a Rank metric. The sum of profit margin is first calculated grouping by Product, then ranked from bottom to top (negative numbers first). The filter on the Rank metric restricts to the first 10 values of Rank.
The Rank function assigns numbers from the minimum value going up to the maximum, since we are in the negative profit range the biggest loser is first. If we wanted to rank positive numbers from big to small, like in top 10 selling products, we should have created a metric and edited the rank parameters in the Metric Editor.
Try it yourself:
- Open the Rank ProductMargin from FactResellerSales metric by double-clicking on it.
- Click on File | Save As, and save it with it a different name like
Rank ProductMargin from FactResellerSales DESC. - In the newly saved metric, highlight with the mouse the formula Rank([Sum ProductMargin from FactResellerSales]), and while it is highlighted right-click on it.
- From the menu that appears, select Rank parameters.
- This will open a dialog box with three parameters, set the first one ASC to
false, click on OK and then on Save and Close.
Create a report with the top five profitable products (as shown in the following image):
