Watch the video demonstration of creating reports of top 10 product by sales amount.
It is a common requirement to create TOP N reports to understand which product or product category or product sub category has maximum amount of sales amount. The video demonstrate how we can create TOP N report both in Excel and Power View using Power Pivot Model.
Are we using DAX TOP N function?
The answer is No. DAX TOP N function return the top N rows of the specified table. We can use this function to create a calculated field/measure for example, SUM of Top 10 products by sales amount. But we cannot just use the top n functions to create the report which says “Give me list of top 10 products”.
Creating Top 10 report in Excel
Actually, it is so easy that you need not modify anything. There is no need to create any other measure. We can create Top N report for any dimension attributes like Product Category / Sub Category/ Color etc within seconds without any changes.
Just use the “Value Filters” option provided by Pivot Table and you are done (Watch video).
Creating Top 10 report in Power View
This is not as intuitive as excel. Here we have to rank the total sales based on each dimension attribute for which you want to create report. For example, if you want Top 10 Product and Top 10 Product Sub category, you need to create 2 measures to “Rank” the total sales by product and subcategory.
After that, those rank is used in report filter to create Top N report (Watch video).
Note: – You can create Calculated measure on the fly in Power View (another blog by Melvin), which eventually gets added to the Power Pivot mode.
DAX function to return ranking: RANKX
In the demo we have used following ranking measure.
- Rank All by Product Sub Category: RANKX(ALL(Product[Product Subcategory]), [Total Line Sales]) or you can use
- Rank Selected by Product Category: RANKX(ALLSELECTED(Product[Product Subcategory]), [Total Line Sales])
Both the formula is valid in different scenario. Choose according to your requirements.