Forum Discussion
Odd results for DIVIDE function in report
I have a simple measure (Actual Mult) where I am just dividing two other measures using the DIVIDE function. Every now and then, our users report odd results coming through on the report from this DIVIDE function (see first record). I have been playing around with a new measure and trying multiple DAX functions, but still no luck. I am assuming this might be a rounding issue of some sort, but I would expect the first record to have an Actual Mult of 0 ( -0.14 / 0 ) and not the massive number we are getting. Any help/direction would be greatly appreciated, as this sporadic error is becoming more prevelant.
[Revenue Actual Total (Multiplier) Agg] (Decimal 0.00)
[Actual Direct Labor (Multiplier)] (Decimal 0.00)
Try this measure. You can use various methods of identifying unusable denominators (e.g., ">-1 and <1").
Actual Mult New = VAR vDenominator = [Actual Direct Labor (Multiplier)] VAR vResult = IF ( ROUND ( vDenominator, 0 ) = 0, 0, DIVIDE ( [Revenue Actual Total (Multiplier) Agg], vDenominator ) ) RETURN vResult
2 Replies
- DataInsights
Super User
Try this measure. You can use various methods of identifying unusable denominators (e.g., ">-1 and <1").
Actual Mult New = VAR vDenominator = [Actual Direct Labor (Multiplier)] VAR vResult = IF ( ROUND ( vDenominator, 0 ) = 0, 0, DIVIDE ( [Revenue Actual Total (Multiplier) Agg], vDenominator ) ) RETURN vResultDataInsights Thank you for the quick reply. Sorry I am just getting back to this.
From my initial testing, this seems to be working for me. I created a separate measure to round the Actual Direct Labor and then I am referencing that in Actual Mult New Calc.
When I added that new measure to the final report, I am getting the following error now, but this is something for me to figure out. Seems like I need to do some optimization in the dataset. Thanks again.
