Report of the Week - Top N Items in Each Category
This week, let us see how to create a report that lists top 5 in each category. You can apply this in various scenarios such as top selling product in each category, top performing agents in each department, top customers in each region, etc.,
Let us see how to create a report of the top 5 customers in each region based on sales.
Rank by Sales
Create an aggregate formula to rank sales in the descending order using the below formula.
|
Sales Rank = Rank(Sum("
Sales"."Sales"
),
'desc'
)
|
This will calculate sales for the dimension in your report and rank the items in descending order based on sales.
Creating the Report
Follow the below steps to create the report.
-
Open the Pivot View Designer.
-
Drop the columns as follows:
-
Rows
-
Region
and
Customer Name
with
Actual.
-
Data - Sales with Sum.
-
Open the
Filters
tab.
-
Drop the Aggregate Formula column
Sales Rank
. Open
Ranges
tab.
-
Click +Add New Range link and create a range as Below 6.
-
Click the Sales Rank column. The Show Data Setting will open.
-
In the
Calculate By
field, select
By Base Fields
.
-
Select the checkbox near the column based on which you want to rank sales.
-
Click
Apply
. Report with top 5 customers in each region will be generated.
-
Using the Themes button, customize the theme as needed.
