Multiple If statement

[jas2728]

Hi All,

I need some help with a below scenario. 

Sale Order      Sales      PURCH Id       Purchase 

SO-92345     25,000     PO-233443       25,000

SO-92346    -25,000     PO-233443      25,000 

SO-92347     25,000      PO-233443      25,000   

Total:            25,000                            75,000                            

Intended Result: 

Sale Order      Sales      PURCH Id       Purchase 

SO-92345     25,000     PO-233443      25,000

SO-92346    -25,000     PO-233443   -25,000 

SO-92347     25,000      PO-233443     25,000 

Total:            25,000                            25,000        

I am using below measure to correct the total of the purchase amounts, Is there a way i can use a statement which checks if the sales amount is negative then to multiply the  Purchase value with -1 in the same statement ?

Please help.

Correct total purchase = if (HASONEVALUE(CUSTINVOICEJOUR[SALESID]),
   [Total purchase],SUMX(VALUES(CUSTINVOICEJOUR[SALESID]),[Total purchase]))

Thanks,

[MFelix]

Hi @jas2728 ,

Why don't you se the absolute value on your calculation:

Correct total purchase =
IF (
    HASONEVALUE ( CUSTINVOICEJOUR[SALESID] ),
    [Total purchase],
    SUMX ( VALUES ( CUSTINVOICEJOUR[SALESID] ), ABS ( [Total purchase] ) )
)

Regards,

MFelix

[jas2728]

Hi MFelix,

Actully the amount in the purchase table is positive, i wanted to see if i can handle the case in BI and convert the repeating value to negative.

[MFelix]

Hi @jas2728 ,

can you explain a little bit better what you want to achieve? In your example all your values are 25K so difficult to understand how it gives you the negative.

What is the rule for multiplying for -1.

Regards,

MFelix