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.
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.