Forum Discussion
Pivot Table SubTotal does not agree with simple SUM operation
Thank 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
- Thank you. I have very limited experience using Power Pivot and calculated columns.
The question then becomes how to get that sum result in Grand Total?To get the sum we need to calculate Grand Total separately. For the row with each Num we calculate as it is (Num has on value in row context), for the global sum all results for the Num and for the selected Months. Measure could be
Three++:=IF ( HASONEVALUE ( trans01to04[Num] ), [Three+], SUMX ( VALUES ( trans01to04[Date (Month)] ), CALCULATE ( SUMX ( VALUES ( trans01to04[Num] ), [Three+] ), KEEPFILTERS ( trans01to04[Qty] ) ) ) )If you need result for the concrete dates when ore more step shall be added inside. Right now in Rows are only
With above measure we have in Global Total
Please check in attached file.
- Thank you for the reply. This is a good solution.
Brad