Exclude Zero results in Overall Calculation?

DAX DAX Calculations
2

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 -

Results of Arrived and Clinic Hours
Results of Arrived and Clinic Hours1164×309 10.6 KB

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 -

Results of Arrived

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 -

Results of Arrived With Correct Totals

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 -

Final Results
Final Results779×344 10.2 KB

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. :slightly_smiling_face:

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