Matching matrix values no longer match when truncated to 2 decimal places. Weird .005 rounding.

[electrichead]

I have a matrix visual with a DAX calculation that provides the values for the matrix.  I wanted to check the formula, so I created a second value column and added a new DAX measure that should give me the same result but using a different calculation.

Both formulas give me 4.565

When I adjust the matrix visual to only show 2 decimal places one value rounds up to 4.57 and the other round down to 4.56.

I'm not using any round functions in the formulas.  Any idea why I'm seeing this?  I would assume adjusting the decimal value shown within the matrix visual only affects the shown formula result, but does it also affect the values within the calculation?

[Anonymous · Accepted solution]

Hi, @electrichead 

Based on your information, I create a sample table:

I tried to simulate three different scenarios, I created some visuals to contrast.

The first matrix is the raw data, showing all the decimals. The second matrix uses the Sum function to simulate what you get after using the measure, and I manually kept three decimal places. But in fact, there are still a lot of decimal numbers behind it.

OriginalMeasure = SUM('Table'[SalesAmount])

Next, create a new measure, using the Round function to keep only two decimal places

RoundedOriginalMeasure = ROUND([OriginalMeasure], 2)

Did you find out? It is not rounded to the decimal set on the surface, it is calculated according to the original data (including all decimals). And its logic is to round the next decimal place where you need to keep a few decimal places. As shown in the diagram, 4.564523, with two decimal places, it will be treated as 4.564 first, and then trade-off, so that 4.56 is obtained.

Therefore, we need to modify the original data with measures or calculated columns to preserve the number of decimal places, force it to be rounded, and retain three decimal places. So that it actually has only three decimal places, with no extra decimals

Round = ROUND(SUM('Table'[SalesAmount]),3)

Instead of formatting it with a few decimal places

Finally, create a metric to keep two decimal places, so that it will all become 4.57

Round 2 = ROUND('Table'[Round],2)

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[electrichead]

Thanks for breaking that down.  I do think I understand what your illustrating, and I appreciate the detail.  I'm not sure if that is quite what I was seeing though.  In your example, 13.6945 was never rounded or displayed as though rounded down to 13.694.

For now I'm going to chaulk this up to an oddity that was transient.  I shared my screen with a colleague and he witnessed it too, but over the last two days after shutting things down and restarting I have not been able to recreate it.  If I can I'll return with more details and screenshots.

[Anonymous]

Hi, @electrichead 

This is because my OriginalMeasure = SUM('Table'[SalesAmount]) and I just formatted it to 3 as a decimal, which is actually still 13.694569. Then, as I said above, when going to a few decimal places, it will be judged by the last digit of a few decimal places. If you were to leave 3 decimal places, then his rounding would be 13.695 based on the 5 after 4. Its strange logic doesn't follow our daily rounding up of the last digit and adding one step by step. Assuming its value is 13.694469, then keeping three decimal places and rounding it to the credential is 4 after 4, and 69 after it is completely ignored. I hope you understand. And I wish you all the best.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[SamWiseOwl]

Hi @electrichead 

Would you be ok sharing the DAX?

Cheers

Sam