Exclude Zero results in Overall Calculation?

I’m SURE this has been covered somewhere but lord help me if I can figure out the right search terms to find a solution that fits what is currently flummoxing me.
The raw data I’m working with currently has incomplete working hours for some of the staff. Thus when I want to plot the weekly productivity rate the weeks that have partial data show extreme numbers.

Example filtered to week of 4/3/2021 for a 450% rate. Page1 tab…
What I really want is the productivity calculation for this week to exclude the zero data knowing that going forward we’ll have more complete data but going backwards it’s incomplete.

Calculate seemed like the right DAX formula but that didn’t like me using a logical statement.
IF statement ends up giving me the same result as I already have if I make the false statement = 0…
Switch doesn’t seem like the correct tool either…

There has to be a way to do this but it’s definitely outside my beginner level knowledge.

WeeklyTest.pbix (199.6 KB)

Hello @gkavesh,

Thank You for posting your query onto the Forum.

Well just couple of days back I exactly addressed this same kind of issue onto the forum. Below is the link of that post provided for the reference. Please feel free to check out that post.

Here’s your solution provieded below - (Let’s analyse this step-by-step)

1). The formula which you had used for “Productivity Rate” is as follows -

``````Productivity Rate =
DIVIDE( [Arrived - 1] , [Clinic Hours] , 0 )
``````

Now, since you’ve complete data for the “Arrived” but incomplete data for “Clinic Hours” this is where things started to go downhill. Below is the screenshot provided for the reference -

So when you were dividing the “Arrived” with the “Clinic Hours” you were getting the individual results correctly but overal results were incorrect since it was dividing “252” with the “56” but actually it should’ve been “50 divided by 56” which equals to “89.29%”. Let’s see how you can achieve this results in the next steps.

2). Firstly, we’ll have to consider only the figures of the “Arrived” against which only “Clinic Hours” have the data. Below is the formula provided for the reference -

``````Arrived - 2 =
IF( ISBLANK( [Clinic Hours] ) ,
BLANK() ,
[Arrived - 1] )
``````

Now, see the results. Below is the screenshot provided for the reference -

If you observe carefully, the above measure evaluates the results only against which “Clinic Hours” have the data but the grand total is wrong. So let’s now fix the grand total.

3). Measure for Fixing the Totals -

``````Arrived - 2 - Totals =
SUMX(
SUMMARIZE(
Therapists ,
Therapists[ShortProvider] ,
"@Totals" ,
[Arrived - 2] ) ,
[@Totals]
)
``````

Note: One of our expert @Greg has already created a post onto the forum pertaining to this topic which covers everything. Below is the link of that post provided for the reference as well. I recommend you to please go through this post for better understanding of the concepts.

Now, see the results of the “Arrived”. Below is the screenshot of the results provided for the reference -

4). Once this is done, now you can divide the “Arrived” with the “Clinic Hours”. Below is the formula provided for the reference - ,

``````Productivity Rate - Revised =
DIVIDE( [Arrived - 2 - Totals] , [Clinic Hours] , 0 )
``````

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference as well.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note:

1). I’ve used the “Measure Branching” technique for simplification purposes rather than writing long and complex formulas.

2). To view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

WeeklyTest.pbix (200.3 KB)

4 Likes

Thank you for the thorough explanation and links to the other forum questions that relate to the same logic.

Slowly but surely my skillset is getting stronger.

• gina