I am trying to build a pivot table using the power pivot and the data model built from two different files.
With this pivot table, I am trying to compare Quarterly 2022 revenue vs. the same period in the prior year. In the data model, I added columns for Q1'22 $ variance, Q1'22 % variance, etc. Formulas were: $ var = [Q1'22] - [Q1'21], and % var = $ var/[Q1'21]. The data model has several rows which are customers and the customers are part of categories.
I created a pivot table from the data model as follows.
1. Rows - customer name
2. Columns - Quarterly revenue for '21 and '22; yoy $ variance, yoy % variance.
![Image Image](/board/proxy.php?image=https%3A%2F%2Ffilestore.community.support.microsoft.com%2Fapi%2Fimages%2F1b0a6134-b8fc-4572-a5f1-2e99319af6e6%3Fupload%3Dtrue&hash=e5b0c32a64bb62b2be97957c0d7d604f)
I've hidden several rows due to the size of the table.
I noticed two things:
1. I have 5 customers that have multiple sales reps even though they are in the same "category". For each of these customers, the pivot table has the incorrect % variance for Q2. In the above example, Core-Mark has -32.6% but it should be -23.8% based on the relevant columns shown. Is the calculation error due to not having the correct formula in the data model to calculate the $ and % variances or would it be due to these 5 large customers having more than 1 sales rep?
2. The category "Conven" total the % variance is completely wrong. It totaled the correctly calculated variances for each individual customer. How can that be corrected?
I appreciate any insight that could be provided.
With this pivot table, I am trying to compare Quarterly 2022 revenue vs. the same period in the prior year. In the data model, I added columns for Q1'22 $ variance, Q1'22 % variance, etc. Formulas were: $ var = [Q1'22] - [Q1'21], and % var = $ var/[Q1'21]. The data model has several rows which are customers and the customers are part of categories.
I created a pivot table from the data model as follows.
1. Rows - customer name
2. Columns - Quarterly revenue for '21 and '22; yoy $ variance, yoy % variance.
I've hidden several rows due to the size of the table.
I noticed two things:
1. I have 5 customers that have multiple sales reps even though they are in the same "category". For each of these customers, the pivot table has the incorrect % variance for Q2. In the above example, Core-Mark has -32.6% but it should be -23.8% based on the relevant columns shown. Is the calculation error due to not having the correct formula in the data model to calculate the $ and % variances or would it be due to these 5 large customers having more than 1 sales rep?
2. The category "Conven" total the % variance is completely wrong. It totaled the correctly calculated variances for each individual customer. How can that be corrected?
I appreciate any insight that could be provided.