Multiplying values

[abukapsoun]

Hi,

Need your support please.

I have the following Table

Reference	P01	P02
X	-1000	500
Y	800	-600

How can i transform the sign of values based on the following condition:

If Reference is X, then - make it + and the + make -

If reference is Y, then keep it as is

Many thanks in advance,

Regards,

[Seenu80]

Hi Experts,

need your support on below i have absolute value in one column

Shkg  Value

H        100.00

S           50.00

if H, i need to do 100.00*-1 if S then 50.00. can you suggest how can i add new column based on above condition.

Regards

Srini

[amitchandak]

@abukapsoun , new columns

new P1 = if([Reference] ="X", -1*[P1],[P1])
new P2 = if([Reference] ="X", -1*[P2],[P2])

[darentengmfs]

Hi @abukapsoun 

You can create a new column with IF function.

P01-New = IF('Table'[Reference] = "X", 'Table'[P01]*-1,'Table'[P01])

P02-New = IF('Table'[Reference] = "X", 'Table'[P02]*-1,'Table'[P02])

[abukapsoun]

Thank you very much, will give it a try.

Will I be able to unpivot the columns P01- New and P02 New ?

[darentengmfs]

@abukapsoun 

If this is a query, you can always unpivot it in Power Query mode.

Power Query > Transform > Unpivot Columns

[abukapsoun]

Hi

I have created the new columns and it is working like a charm. However when I go to query editor to unpivot the columns, the newly created column does not appear in the table. I assume because they were created from a formula ? But then how can I unpivot them ?

I want to have them in the form of:

Thanks,

[darentengmfs · Accepted solution]

Hi @abukapsoun 

There are 2 ways to do this.

1. You can unpivot the columns, then create the new column with the IF function in DAX
2. Or, you can create the IF function in Power Query Editor before unpivoting: Power Query > Add Column > Custom Column > Formula: if [Reference] = "X" then [P01]*-1 else [P01]

**Update: formula amended as needed

[abukapsoun]

I guess we are almost there, 

I tried creating the formula:

IF [Reference] = "X" then [P01]*(-1) else [P01]

But I am getting the following error on P01: Token Eof expected. 

Any idea?

[abukapsoun]

i changed IF to if and it worked 🙂 thanksss