Subtotal includes blank values

[Leh]

Hello,

I need some help getting the subtotal to show the correct value in a Table Matrix by using DAX if possible, rather than using the filter on the page.

Below is a screenshot of my expected result and my current "workaround".  My concern with the current workaround is that placing a filter on the page may cause issues with other measures that I will add to the report.

Expected Results (even without page filter)

The first column calculates all the time entered by a technician for a particular job.  The second and third columns calculate the total hours entered by all technicians for a particular job.  The difference between the second and third columns is that the former excludes the blank lines if the first column is blank.

Below is the result if I remove the page filter, notice that the Hours Blank measure calculates each line correctly, but the subtotal is off.

Wrong subtotal calculation

I wanted the report to show the Subtotal for Hours Blank measure excludes the values of the Labor Hours that the technician has not entered time against.  This is just the tip of the iceberg because later on, I will add another measure for Cost and Billing (this measure has no tie to the Technician but to the Job Number); that is why I wanted to write a measure that is not dependent to the Page Filters.

Here is the link to the sample data in the Excel file.   Sample Data Excel 

Here is the link to the PBIX. Subtotal and Grand Total for Job and Technician.pbix 

I would greatly appreciate any help.

[Leh]

Hi @grantsamborn ,

I am not sure what happened there, but can you try the link below?

PBIX Link 

Thank you.

[grantsamborn]

Hi @Leh 

That worked.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

Hours Blank = SUMX(FILTER(VALUES(Jobs[Job  Number]),[Technician Labor Hours]<>BLANK()),[All Labor Hours])

Hope this helps.

[Leh]

Thank you very much @Ashish_Mathur .

This has worked wonders for my report.

I just do not want to blindly follow the DAX measure that resolved my issues.  If you have time and patience, can you indulge me by answering if I understood the measure correctly?  I mean I tried both SUMX and FILTER in my testing but somehow didn't get the desired result as your measure has.  I guess I just couldn't follow the concept of sequence or how to envelope the DAX code to get the desired result.

Looking at the measure, the "FILTER(VALUES..." evaluates each row if the [Techinician Labor Hours] at the Job Number level is blank and excludes that row in summing up the [All Labour Hours] measure that already strips all the filters by using "ALL(..". Is that the right interpretation? 

Thank you again.

[Ashish_Mathur]

You are welcome.  That is correct.

[Greg_Deckler]

@Leh First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Maybe this specifically: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[Leh]

Thank you, @Greg_Deckler .

I'll see what I can dig up from the links provided.

I also voted as requested.  The idea was posted 5 years ago, not sure if one vote helps.  .

[grantsamborn]

Hi @Leh 

Both are links to SampleData.xlsx