Exclude Zero results in Overall Calculation?

[gkavesh]

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.

Thanks in advance -

WeeklyTest.pbix (199.6 KB)

[Harsh]

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)

Forecast based on current YoY growth

Hello @NickvanDijk, Ok, so here’s the reason why that “YoY Growth” figure went negative and Forecast started to show vague results when future dates were selected. I’ve used the “Measure Branching” technique here to resolve this issue rather than writing complex formula. Let’s analyze this step-by-step. - 1). We’ve Sales till 6th Jan, 2021 so the sales measure is like this. Total Sales = SUMX( Sales , Sales[Quantity] * RELATED( 'Product'[Current Price] ) ) 2). Now, to calculate the To…

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[gkavesh]

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