Forum Discussion
Round per row or per total
Hi all,
Is there a rule about rounding in BI?
I have a sales table with quantity in a format of 0.0000 but I want to display them in BI as 0.00
Easy: You round them.
But I can round them in the data model per row. so each quantity becomes a 0.00
Or I can count all quantity's per product/category etc... and then round them in this measure.
The last solution makes the reports different. Meaning. The totals of quanity based on product != to the ones per category. Becouse of the rounding is different.
The first solutions looks like it is the best solution. You round each row. Now you can make totals as much as you want. They will always be the same.
Huge but: When you check the actual item quanity in the erp system. The solution where you round everything per row will not be the same as the total quantity in the erp system.
So it is choosing between: Match the erp system but have different totals per visual
Or Match all visuals but have a slight difference with the erp system
Or Do not round at all
Or something i did not know about.
kind regards.
Roel
3 Replies
v-lili6-msftCommunity Support
hi, Anonymous
For your data in ERP is in a format of 0.0000, I would suggest you round them in this measure.
when you round them in data model for 0.00, but when they do a calculation, they are still used by 0.0000 in calculating process, not use 0.00 format. so in the final result, it will lead to a little bit of computational error in total.
Best Regards,
Lin
I have a similar issue. We are trying to show sales by year and by asset type. e.g.
Raw data
2018 - Manufacturing - 20,320,300
2018 - Retail - 40,200,400
2018 - Health - 30,200,150
2018 - Total - 90,750,850
Similar numbers for 2017,2016 etc.
The challenge is we need to represent in (000) or thousands. So, if i round at line item level I get
2018 - Manufacturing - 20,320 (rounded to nearest thousands)
2018 - Retail - 40,200 (rounded to nearest thousands)
2018 - Health - 30,200 (rounded to nearest thousands)
2018 - Total - 90,750 (rounded to nearest thousands - but now the individual line items don't add up to this they add to 90,720)
This issue will obviously cascade further into overall total for all years.
What is the best practice to deal with these kind of rounding challenges?
Thanks in advance
v-lili6-msft: I did try your solution. But the problem here is that we also compare the full totals of certain figures with other totals. In my case this is. Working hours with worked hours. worked hours splitted in invoiced hours and non invoicable hours. When they see all the totals and I did not round the line items to 2 digits. You cant recalculate this. worked hours should be exactly the sum of invoiced hours and invoicable hours. If they are round at the total level. It will not be exact. And then the managers complain that the figures are wrong.
So I just round to 2 digits at this moment. Then evertything is exact in Power BI. The ERP system and BI is a few digits off. But for now. I can explain the rouding better this way.
If there is a best practice. I would love to know. Invoiced for example have the same issue. An invoice can be rounded at the line totals. And at the sum of the line totals.
Kind regards.