IF Negative value

[AllanBerces]

Hi good day can someone help me on my calculated column or can i do the same in PQ.
 - IF the Progress is Negative and the Work % equal to 100 then used the Plan value, IF the Work % is not equal to 100 then copy the Progress value

RESULT

Thank you

[Ritaf1983 · Accepted solution]

Hi @AllanBerces 
You can add custom column with the formula :
if [Progress]<0 and [#"Work %"]=100 then [Plan] 
else [Progress]

Please note :
You don't need a column if the goal is to put the calculation in visualization, you can just create a measure like :

test measure =

if( max('Table'[Progress])<0 && max('Table'[Work %])=100,sum('Table'[Plan]),sum('Table'[Progress]))

More information about differenses between calculated columns vs measures here :
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

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

The pbix with the example is attached

[ryan_mayu · Accepted solution]

@AllanBerces 

you can try this

= if [Progress]<0 and [#"Work%"]=100 then [Plan] else if [#"Work%"] = 100 then [Progress] else if [#"Work%"]=0 then [Plan] else [Progress]

[Ritaf1983 · Accepted solution]

Hi @AllanBerces 
You can add custom column with the formula :
if [Progress]<0 and [#"Work %"]=100 then [Plan] 
else [Progress]

Please note :
You don't need a column if the goal is to put the calculation in visualization, you can just create a measure like :

test measure =

if( max('Table'[Progress])<0 && max('Table'[Work %])=100,sum('Table'[Plan]),sum('Table'[Progress]))

More information about differenses between calculated columns vs measures here :
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

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

The pbix with the example is attached

[AllanBerces]

Hi @Ritaf1983 thank you very much for the reply, a follow up query if i want to add new condition (IF the Work % is equal to 0 then copy the Plan Value else Progress Value). Thank you

[Ritaf1983 · Accepted solution]

Hi again , then the formula will be :
PQ:

You can add custom column with the formula :
if [Work%]<0 =0 then [Plan] 
else [Progress]

 Dax :

Test measure =

if(  max('Table'[Work %])=0,sum('Table'[Plan]),sum('Table'[Progress]))

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

[AllanBerces]

Hi @Ritaf1983 @ryan_mayu thank you very much for the reply, working as i need.

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@AllanBerces 

you can try this

= if [Progress]<0 and [#"Work%"]=100 then [Plan] else if [#"Work%"] = 100 then [Progress] else if [#"Work%"]=0 then [Plan] else [Progress]