# Fundamental calculation understanding Issue

#### tusharmehta

##### New Member
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
-------------

 DocNo Lcode DocDate Icode Type Discount Amount 1 LOC1 01/01/2015 ITM0001 01 1,000.00 1 LOC2 01/01/2015 ITM0001 02 200.00 25,800.00 2 LCO1 01/01/2015 ITM0002 01 2,080.00 2 LOC1 01/01/2015 ITM0003 01 4,800.00 2 LOC1 01/01/2015 ITM0004 01 300.00 2 LOC1 01/01/2015 ITM0005 01 5,500.00 3 LOC3 06/01/2015 ITM0002 02 687.00 55,000.00 1 LOC4 06/01/2015 ITM0003 02 50,000.00 3 LOC2 25/02/2015 ITM0003 03 - 46,000.00 1 LOC1 26/03/2015 ITM0006 01 890.00 3 LOC3 27/03/2015 ITM0002 03 50,000.00 4 LOC4 28/03/2015 ITM0004 02 89.90 888.00

<tbody>
</tbody>

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 Labels 01 02 03 Grand Total ITM0001 1000 25800 26800 ITM0002 2080 55000 -50000 -50000 ITM0003 4800 50000 -46000 -46000 ITM0004 300 888 1188 ITM0005 5500 5500 ITM0006 890 890 Grand Total 14570 131688 -192000 -338258

<tbody>
</tbody>

----------------------------------------------------------
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

<tbody>
</tbody>

### 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.

total:=SUMX('Transaction',[NetValue])

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:
Code:
``````NetValue :=
SUMX (
'Transaction',
IF (
'Transaction'[Type] <> "03",
'Transaction'[Amount],
'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

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).

Ozeroth, Thank you very much and appreciate your details. It is has clear my confusion while using sumx.

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.

1,203,026
Messages
6,053,113
Members
444,639
Latest member
xRockox

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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