I am trying to create a % On Time Delivery measure, but its not working. I have a column (ATS) that shows if an order is Early (negative number), On Time (0), or Late (positive number) by doing a DATEDIFF between planned and actual delivery date.
I have another column (ATS On Time) that is
=IF(Sheet[ATS] <= 0, 1, 0)
So it has a 1 if the status is Early or On Time, and a 0 if it is Late
I have another column (ATS Count) that is
=IF(NOT ISBLANK(Sheet[ATS]), 1, 0)
So it has a 1 if the row has an order status, and a 0 if it is blank
So I tried creating a measure (% On Time) that is
=DIVIDE(SUM(Sheet[ATS On Time]) , SUM(Sheet[ATS Count]))
but it is showing 100% when it definitely should be 50%. What am I doing wrong?!
There's also the possiblity that you are displaying the result as a whole number. .5 rounds up to 1, which would give you this behavior. If you add another late delivery to your data, does the measure drop to zero?