Fundamental calculation understanding Issue

[tusharmehta]

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>

Thanks in Advance.

 

[Kazlik]

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

 

[Ozeroth]

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 :=
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​

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

 

[tusharmehta]

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

 

[tusharmehta]

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.