Forum Discussion
IF Negative value
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
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 attachedyou 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]
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.
6 Replies
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 attachedHi 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
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]