Reporting from the Smaller Side of the Relationship

March 03, 2023 - by Bill Jelen

Problem: I am trying to report sales and quota. My sales table has hundreds of records. The Quota table has just one record per region. The pivot table created through the Data Model is not working.

Strategy: In the data below, both column C and Column E were originally called Region. For this example, those fields have been named differently, The relationship is between Region_S and Region_Q.

The first table is an invoice register, with Invoice, Amount, and Region. The second table has one row per region with Region and Quota. — Figure 1049. The table on the left is hundreds of records.

If you use Region_S in the pivot table, the Quota numbers will be incorrect. Change the pivot table to use Region_Q from the Quota table and they will be correct.

If you build a pivot table using Region from the larger table, the quota from the second table is overstated. Instead, use Region from the smaller table and the numbers are correct. — Figure 1050. The report will be correct or not depending on which Region you use.

I have to admit, as someone who formerly dabbled in Access, I expected a wrong answer in the top example. But I would have expected the Central region quota of $300,000 multiplied by the 213 records for the Central region, producing $63,900,000.

This article is an excerpt from Power Excel With MrExcel

Title photo by Monty Allen on Unsplash