Forum Discussion

bracurrie's avatar
bracurrie
Copper Contributor
May 19, 2024

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 19, 2024

bracurrie 

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.75
bracurrie's avatar
bracurrie
Copper Contributor
May 19, 2024

SergeiBaklan 

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 20, 2024

    bracurrie 

    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

    • bracurrie's avatar
      bracurrie
      Copper Contributor
      May 20, 2024
      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?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 20, 2024

        bracurrie 

        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.

Resources