Forum Discussion
Pivot Table SubTotal does not agree with simple SUM operation
I have created a pivot table using one table and a calculated column using Power Pivot. Field "Three+" is displaying correctly but the sub total is incorrect. The other fields are displaying correct subtotals.
What am I missing?
Thanks
Brad
7 Replies
I didn't find calculated column and subtotals, perhaps you mean measure Three+ and Grand Total. Also not sure how did you find that the result is wrong. To check
Let remove filters from PivotTable and refresh.
With that we have 12126 records and Grand Total for COUNTA(trans01to04[Item]) correctly shows this number.
Three++ you defined as
Three+:=if([Count of Item]=1,3,(([Count of Item]-1)*.75)+3)
= IF( 12126=1, 3, 3+(12126 - 1)*.75) = 3+12125*.75 = 9096.75
Above is returned by PivotTable
By the way, you may simplify the measure to
Three+:=3 + (COUNTA(trans01to04[Item])-1)*0.75Thank you for your response.
I did implement a filter to look only at Jan records. Before that I was using a simple filter to eliminate records that had 0 Qty.
Please look at Row 1417 at the totals Column Three+ has a result that isn't a sum of the values from the calculated column.
I do not believe your simplified formula will work because each transaction has to be calculated individually. The first Item that has a Qty is charged $3 and each item after that is charged $.75. However, I do not have any experience with COUNTA.
Grand Total doesn't sum the result, it applies the same formula for the total as for each record. If refresh the PivotTable, you have 6570 in Grand Total for Count of Items. Three+ in Grand Total will be calculated as
=(6570-1)*.75+3 which gives 4929.75, exactly the same what PivotTable returns
