Formula returning Incorrect result

[Anonymous]

Can anyone explain why am I getting an incorrect result in some of the rows (Progress_Status_V_Baseline) like the one highlighted (row 59)? 

Any sugestion of how to fix it, as I am then getting wrong % when analysing the data.

Thanks guys

[BA_Pete]

Hi @Anonymous ,

Can you share the calculation you're using to generate the [Progress_Status_V_Baseline] column please?

Your screenshot only shows the [Progress_Status_V_Target] calculation.

Pete

[Anonymous]

Hi @BA_Pete = Table.AddColumn(#"Filtered Rows", "Progress_Status_V_Baseline", each if ([Value] = -1) then ("-") else if ([Value] < ([Baseline_ToFill] + [Improvement])) then ("Below") else ("Meet or Above"), type text)

[BA_Pete]

Hi @Anonymous ,

I see your point, that is a bit odd.

Maybe try flipping the calculation to see if that helps?

if [Value] = -1 then "-"
else if [Value] >= [Baseline_ToFill] + [Improvement] then "Meet or Above"
else "Below"

FYI, you don't need all those brackets in your code. The if..then..else keywords split everything out correctly for you.

Pete

[Anonymous]

I`ve tried to flip it but I`m getting the same result...

[mahoneypat]

Your result seems correct, as 9.6 is <= 10.2 and should result in "Below". What is the issue?

Pat

[Anonymous]

Hi @mahoneypat sorry, I was showing the wrong step in the 1st screenshot.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please try this code. Use Number.RoundDown or Number.Round to round the result of ([Improvement]+[Baseline_ToFill]) to have a specifc decimal digits. Based on my test, specifying the digits number to 1/2/3/4/... will all bring you the correct result. 

= Table.AddColumn(#"Added Custom", "Custom.1", each if [Value] = -1 then "-" else if [Value] < Number.RoundDown([Improvement]+[Baseline_ToFill],1) then "Below" else "Meet or Above")

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

It works, thank you for you help!

[mahoneypat]

I don't have an explanation for why you are seeing that. Can you share the whole query from the Advanced Editor, so we can see the steps prior to this one?

Pat