Forum Discussion
Rounding question
Hi,
I need some help figuring out how the ROUND function works. I have the following measure with the output shown below:
When I wrap the r1 value inside a Round function with a decimal of 1, I am not getting
0.2
0.3
1.8
but instead I am getting the following output:
Any help would be most appreciated.
Okay i missed that the round was within the iterator of the sumx function. Can you move the round function out of the expression like so:
test =
ROUND (
SUMX (
Table,
VAR r1 = IF ( Table[Column] = "Tax", Table[Column2], 0 ) RETURN rt1
),
1
)Or do you actually need to round the number before summing them together?
More info on Variables in DAX: https://www.sqlbi.com/articles/variables-in-dax/
If this post helped, please mark my post as a solution
Regards,
Thomas Hanson
Individual Contributor
As ROUND is within SUMX it is evaluted for each row in HoldingsReportData before all eveluated rows summed up together in the total row. If you want the total to rounded, wrap it around the SUMX expression instead.
5 Replies
Hello,
I wouldn't expect this behavior from the ROUND function either. Can you please do a couple checks to make sure 8 wasn't entered in:
1. Under Measure tools ribbon > Formatting, make sure either Auto or 1 is shown
2. In Format Visual pane > Specific column > Values > Value decimal places shows Auto or 1
What happens if you put the measure in a card visual?
If this post helped, please mark my post as a solution
Regards,
Thomas Hanson
Individual Contributor
Thank you for your response. Inside the Ribbon and the Visual, I have set the decimal to 1 and Auto respectiviely but the behavior continues. Per your suggestion to put the measure inside a Card visual. The output below shows the output without the rounding:
With the rounding, the output is as follows:
Okay i missed that the round was within the iterator of the sumx function. Can you move the round function out of the expression like so:
test =
ROUND (
SUMX (
Table,
VAR r1 = IF ( Table[Column] = "Tax", Table[Column2], 0 ) RETURN rt1
),
1
)Or do you actually need to round the number before summing them together?
More info on Variables in DAX: https://www.sqlbi.com/articles/variables-in-dax/
If this post helped, please mark my post as a solution
Regards,
Thomas Hanson
Individual Contributor
As ROUND is within SUMX it is evaluted for each row in HoldingsReportData before all eveluated rows summed up together in the total row. If you want the total to rounded, wrap it around the SUMX expression instead.
Sorry for the late reply hansontm and danextian . Long weekend. I hope it's ok if I mark both as the answer. Wrapping the ROUND function outside of SUMX, didn't solve it but as both of you pointed out, it could be a iteration thing. It appears the numbers are wrong anyways when running the same report on the sql server side. Again, thank you for taking time looking at this question.
