Getting a Percentage Measure to Show 0% for True 0s

Anonymous — Fri, 08 Nov 2019 21:35:15 GMT

I'm trying to create a measure that tracks whether an event was completed on time. For each event, I have an event completion date and an event due date in my data. If an event's completion date is <= the due date, that event is considered to have been completed on time. If the completion date is > the due date, that event did not complete on time. If an event has no completion date, I do not want this measure to calculate since it's not applicable.

In my data, I have an [Events_Completed] field that returns a 1 if the event's completion date is populated. I also have an [Events_Completed_On_Time] field that returns a 1 if the event has a completion date, a due date, and the completion date is <= the due date; it returns a 0 if the event has a completion date, a due date, and the completion date is > the due date; and it returns nulls if the completion date and due date are not populated.

In AAS, I have an [Events Completed] measure that is calculated as SUM ( 'Events'[Events_Completed] ), and I have an [Events Completed On Time] measure that is calculated as SUM ( 'Events'[Events_Completed_On_Time]). Finally, my [Events Completed On Time %] measure is DIVIDE ( [Events Completed On Time], [Events Completed] ).

When I view the percentage measure at a high level, everything looks good. When I go down to the level of the individual event, however, my client wants to see 100% if an event completed on time, 0% if an event did not complete on time, and a blank if the event has not completed. The problem is that the events that have a 0 for [Events_Completed_On_Time] and a 1 for [Events_Completed] in my data are returning as blanks when placed in a visual when I expect to see 0% instead.

I know that this is a quirk of Power BI, but I haven't been able to correct this with any of the usual tricks. I can't just do DIVIDE ( [Events Completed On Time], [Events Completed] ) + 0 because that will give me 0% for everything, even if an event hasn't completed. I've also tried IF ( NOT ( ISBLANK ( [Events Completed On Time] ) ), DIVIDE ( [Events Completed On Time], [Events Completed] ) + 0 ), but that still returns either 100% or blanks.

Is there some way I can alter this calculation so that I see 100% if an event was completed on time, a 0% if the event completed and was not on time, and a blank if the event has not yet completed?

Re: Getting a Percentage Measure to Show 0% for True 0s

Anonymous — Mon, 11 Nov 2019 11:04:54 GMT

EventCompleted = VAR comletedEvents = IF(max(Event[CompletionDate])<>BLANK(),1,0) var ontimecompletedEvents = IF(AND(MAX(Event[CompletionDate])<=MAX(Event[DueDate]),comletedEvents=1),1,0) RETURN IF(comletedEvents=1,DIVIDE(comletedEvents,ontimecompletedEvents,0))

Re: Getting a Percentage Measure to Show 0% for True 0s

ukare1996 — Mon, 15 May 2023 07:18:44 GMT

did you manage to figure this out? I am having the same issue?

topic Re: Getting a Percentage Measure to Show 0% for True 0s in DAX Commands and Tips

Getting a Percentage Measure to Show 0% for True 0s

Re: Getting a Percentage Measure to Show 0% for True 0s

Re: Getting a Percentage Measure to Show 0% for True 0s