Rounding Issue ( I think)

[KG1]

Hi

In my data the Orginal value and New AFP Value columns have a number of decimal places. 

I used the rounding function to 2 decimal places, then I created a new custom column (Status) which says if Original Value = New AFP Value then "Matched Payment" else "Variance"

In the example below it shows Variance when it should say "Matched Payment"

Ref	AFP Value	Original Value	New AFP Value	Status
12345	843.8	3372.72	3372.72	Variance

Any advice welcome - thank you in advance

[PaulDBrown · Accepted solution]

You probably need one of the ROUND functions to convert the values. If you use the formatting options it just creates the format for displaying purposes, but doesn't actually convert the number itself.

https://dax.guide/round/ 

[v-xiaotang · Accepted solution]

Hi @KG1 

Thanks for reaching out to us.

If you use the round() function instead of just adjusting the display format, the result should not be wrong.

Could you share the sample file? so that we can test it to find the reason.

Kindly note: You can replace the raw data with bogus data to protect your private information. Thanks 🙂

Best Regards,

Community Support Team _Tang

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

[KG1]

Hi

I used the round.up fucntion and it fixed the problem

Number.RoundUp([AFP Value],2)

Thanks for your help

[PaulDBrown · Accepted solution]

You probably need one of the ROUND functions to convert the values. If you use the formatting options it just creates the format for displaying purposes, but doesn't actually convert the number itself.

https://dax.guide/round/ 

[juncco888]

What is the data type for AFP Value Original and Value New AFP?   Are they the same?  Did you round both to 2 decimal places?

[KG1]

Hi

Yes both rounded to 2 and both decimal number type

[juncco888]

I tried to recreate the error with data I made up and could not duplicate it.  I created two columns and then a conditional column in power query and got the expected result.

I would be happy to try and assist further but would need your sample data and the specific DAX you attempted to use.  Please post if possible.

Thanks

Kyle