Previously, we saw how to create a report for Top N Items in Each Category . Now let's see how to do comparative analysis along with top values with the following reports as examples.  

• Comparing Current and Previous Month Customers' Invoices - This report compares two sets of values and gets the top and bottom N results. 
• Sales in Percentage against Top Sales - This report gets the percentage of all values in comparison with the top value. 

Report 1: Comparing Current and Previous Month Customers' Invoices

This report ranks the customers whose invoices have grown in comparison with the previous month. This helps you identify customers who tend to purchase more from you and also the customers who will purchase less.

Let's see how to create the above report.

• Create Aggregate Formulas
• Create Report

Create Aggregate Formulas

Create the following Aggregate formulas by clicking Add > Aggregate Formula to get the required data.

• Current Month Sales 
• Previous Month Sales
• Difference in Sales between current and Previous Month
• Rank Sales

The following formula gives you the current month sales. 

Current Month Sales

The following formula gives you the previous month sales. 

Now compare the current and previous month sales using the following formula, which gives the difference between current and previous month sales.

Rank Sales

Add an aggregate formula to rank the sales in ascending order. From this, you can get the botton N items by sales. 

Have another aggregate formula to rank sales in descending order to get the top N items by sales.

Create Report

Now, the necessary reports can be created using the above aggregate formulas.

1. Open the Pivot Table designer. 
2. Drop the columns as given below.
   
• Row - Customer Name with Actual
• Data - Current Month Sales, Previous Month Sales  and Sales Diff Previous Month with Actual
  
3. Filter the Top or Bottom 5 Customers by adding the Rank of Sales columns in the Filters tab. 
• Add Rank of  Sales - Desc and filter Range below 6 to filter top 5 customers. 
• Add Rank of Sales - Asc and filter Range below 6 to filter bottom 5 customers. 
4. The report will be generated as below. Hide the Total. 
   
   
   
   
5. Open Dashboard designer and add both the reports.
   
   
   
   

Report 2: Comparing Sales in Percentage against Top Sales

As the second example, let us see how to create a report that compares each value against the top value. For example, comparing each sales person's sales with the top performing sales person's sales. 

Find the Top Sales

The above formula will calculate sales for the dimension in your report (Region, in this example) and return the top value for each section. 

The above report displays the top sales made by the salespersons in each region.

Calculating Sales in Percentage against Top Sales

Now you can calculate the Sales in Percentage against Top Sales using the Report Formula. To invoke the Report Formula dialog, click the highlighted icon. 

Use the below formula to calculate the Sales in Percentage against Top Sales.  

A report will be generated. 

Hide the Totals using the Show/Hide button and customize the Theme as needed.