Fundamental calculation understanding Issue


New Member
May 12, 2014
Hello Everyone,

I am finding fundamental understanding issue, While doing calculation for below mentioned table.

To would like to generate Grand Total value with using below mentioned type calculation (01 + 02 - 03)

Data Sheet

DocNoLcodeDocDateIcodeType Discount Amount
1LOC101/01/2015ITM000101 1,000.00
1LOC201/01/2015ITM000102 200.00 25,800.00
2LCO101/01/2015ITM000201 2,080.00
2LOC101/01/2015ITM000301 4,800.00
2LOC101/01/2015ITM000401 300.00
2LOC101/01/2015ITM000501 5,500.00
3LOC306/01/2015ITM000202 687.00 55,000.00
1LOC406/01/2015ITM000302 50,000.00
3LOC225/02/2015ITM000303 - 46,000.00
1LOC126/03/2015ITM000601 890.00
3LOC327/03/2015ITM000203 50,000.00
4LOC428/03/2015ITM000402 89.90 888.00


After trying below mentioned formula to convert type 03 to negative value for correct calculation I am still getting getting wrong grand total result for ITM0002 and ITM0003.

Formula which I though will help me:

NetValue:=SUMX('Transaction',IF('Transaction'[Type]<>"03", CALCULATE(SUM('Transaction'[Amount])),SUM('Transaction'[Amount])*-1))

Row Labels010203Grand Total
Grand Total14570131688-192000-338258


Expected result value would be mentioned below:

Row Labels 01 02 03 Grand Total
ITM0001 1,000.00 25,800.00 26,800.00
ITM0002 2,080.00 55,000.00 -50,000.00 7,080.00
ITM0003 4,800.00 50,000.00 -46,000.00 8,800.00
ITM0004 300.00 888.00 1,188.00
ITM0005 5,500.00 5,500.00
ITM0006 890.00 890.00
Grand Total 14,570.00 131,688.00 -96,000.00 50,258.00


Thanks in Advance.

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
When using the grand total in your pivot table it is just the evaluation of the measure in that column or row without the filter (from whatever you have on rows or columns) applied and not the total sum of that column. If you want the sum of the values do something like this.

Upvote 0
Tusharmehta, nesting SUM/SUMX is causing the problem. Also in this case there's no need for the CALCULATE if you avoid nesting SUMs.

A correct formula to do what you're trying to is:
NetValue :=
    IF (
        'Transaction'[Type] <> "03",
        'Transaction'[Amount] * -1

Why was there a problem in the original formula?

First off, SUM is just an abbreviated way of writing a SUMX, and SUMX iterates over all rows of a table in the current filter context.
i.e. SUM(Table[Column]) is equivalent to SUMX(Table, Table[Column]).

So putting a SUM inside a SUMX basically did two loops over the same table (like a crossjoin of the table with itself):

For every row of 'Transaction' in current filter context
For every row of 'Transaction' in the same current filter context
Add up 'Transaction'[Amount]

Oh and the row context created by the outer SUMX doesn't become filter context of the inner SUMX (unless you wrap the expression to be summed inside a CALCULATE), so if there are k rows intended to be summed, you actually end up summing those rows k times.

For the cells of the pivot table (with a single Icode and Type), it happened that there was only one row of 'Transaction' in the filter context, so the nested SUMs actually worked fine for those cells (summing one row once).

However for the Grand Totals, there were multiple rows of 'Transaction' in the filter context.
For Type 03 where there were two rows in the filter context, the Grand Total was summing each row twice:

For row with Amount = 50,000
Sum -50,000 + -46,000​
Plus for row with Amount = 46,000
Sum -50,000 + -46,000
giving a total of -192,000

(The use of CALCULATE for Type <> 03 actually made the other sums work since it converted the outer row context to filter context for the inner sum).

I guess the message is to avoid nested SUMs or SUMXs over the same table, unless you are trying to do a calculation involving every row paired with every row (i.e. a crossjoin).
Upvote 0
Ozeroth, It is working as expected.

Does it possible same calculation using other method like calculate or something else I am tying to look into possibility to user more filters.

Like i request to know in my new latest post.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back