Trying to include a value twice once positive and once negative.

[Anonymous]

Hi Guys,

I have a sample data which has first 3 columns (A-C) from raw data and one column D is calculated based on if statement. 

A	B	C	D
Inv_no	Code	Amount	Bucket
0003655	ABC	666	APPLE
0003656	DEF	213	APPLE
0003657	GHI	10	PEAR
0003655	JKL	58	BANANA
0003656	ABC	533	APPLE
0003657	DEF	94	APPLE
0003655	GHI	156	PEAR
0003656	ABC	222	APPLE
0003657	DEF	234	APPLE
0003655	GHI	247	PEAR
0003656	ABC	259	APPLE
0003657	DEF	272	APPLE
0003655	GHI	285	PEAR
0003656	JKL	297	BANANA
0003657	ABC	310	APPLE
0003655	DEF	323	APPLE
0003656	GHI	335	PEAR
0003657	JKL	10	BANANA
0003655	ABC	58	APPLE
0003656	DEF	533	APPLE
0003657	GHI	94	PEAR
0003655	JKL	247	BANANA
0003656	ABC	259	APPLE
0003657	DEF	272	APPLE
0003655	GHI	156	PEAR
0003656	JKL	222	BANANA
0003657	ABC	234	APPLE
0003655	DEF	285	APPLE
0003656	GHI	297	PEAR
0003657	JKL	310	BANANA
0003655	ABC	323	APPLE
0003656	DEF	10	APPLE
0003657	GHI	58	PEAR
0003655	JKL	533	BANANA
0003656	ABC	94	APPLE
0003657	DEF	247	APPLE

Column D is =IF(OR(C4="ABC",C4="DEF"),"APPLE",IF(C4="GHI","PEAR","BANANA"))

I have summarized the above data based on bucket(Column D). 

APPLE	$5,440
BANANA	$1,677
PEAR	$1,638

What i am trying to do is: to include code "GHI" and its values in Bucket "APPLE" as well but with negative value. 

Amount based on codes are: 

ABC	$                  2,958
DEF	$                  2,482
GHI	$                  1,638
JKL	$                  1,677

So We have to take out $1,638 code "GHI" value from bucket "APPLE".

New summary table should look like this.

APPLE	$                  3,802
BANANA	$                  1,677
PEAR	$                  1,638

We are doing this to reconcile the financial numbers for P&L statement. The above data set is hypothetical and has nothing to do with any real data.

Need help urgently.

Thanks

RM.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure as follows.

sum_negative value = 

var x1=SUMX(FILTER(ALL('Table'),[Bucket]=SELECTEDVALUE('Table'[Bucket])),[Amount])

var x2=SUMX(FILTER(ALL('Table'),[Code]="GHI"),[Amount])

return

IF(MAX('Table'[Bucket])="APPLE",x1-x2,x1)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

You can create an amount measure with the following formula. 

SUMAMOUNT = SUM(data[Amount]) - IF(SELECTEDVALUE(data[Bucket]) = "Apple",calculate(SUM(data[Amount]),FILTER(ALL(data),data[Code] = "GHI")))

[selimovd]

Hey @Anonymous ,

you can do that in 2 steps.

First you need to create the column:

D =
SWITCH(
    TRUE(),
    myTable[Code] = "ABC" || myTable[Code] = "DEF", "APPLE",
    myTable[Code] = "GHI", "PEAR",
    "BANANA"
)

Then you have to do the measure for the calculation:

MyMeasure =
VAR vTable =
    ADDCOLUMNS(
        VALUES( 'myTable'[Bucket] ),
        "SUM",
            IF(
                'myTable'[Bucket] = "APPLE",
                CALCULATE( SUM( 'myTable'[Amount] ), 'myTable'[Bucket] = "APPLE" ) - CALCULATE( SUM( 'myTable'[Amount] ), 'myTable'[Bucket] = "PEAR" ),
                CALCULATE( SUM( 'myTable'[Amount] ) )
            )
    )
VAR result =
    SUMX( vTable, [SUM] )
RETURN
    result

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[amitchandak]

@Anonymous , create a new table code where you have GHI in both bucket with multiple 1, and -1. same way multiple 1 for other.

Join both tables on code and try this measure

Code,Bucket , multiple

sumx(Table, Table[Amount]*related(code[multiple])) or

or

sumx(Table, Table[Amount]*maxx(relatedtable(code),[multiple]))