I have a problem with a calculation in a pivot table. My result should look like the following table
Quarter
Q1
Q2
Q3
Q4
Total
Customer
Sales Amount
AVG
Sales Amount
AVG
Sales Amount
AVG
Sales Amount
AVG
Sales Amount
AVG
200
100
150
100
200
550
137,50
305
50
100
120
130
200
150,00
402
100
0
200
0
300
150
303
140
160
300
0
600
200
For me is the value in the AVG column in each quarter not important. For me is really important the correct result in the total AVG column. The sum over all quarters should divided by the number of quarters with sales amount, not divided by the shown quarters.
Example:
The sales amount sum of customer 402 is 300€. This sales amount comes from Q1 and Q3. So the sum must be divided by 2 and I get the result 150€.
