Forum Discussion

New Member

3 years ago

Solved

Subtotal includes blank values

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 expect...

3 years ago

Hi,

Write this measure

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

Hope this helps.

New Member

3 years ago

Hi grantsamborn ,

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

Thank you.

Solution Sage

3 years ago

Hi Leh

That worked.

Thanks

Ashish_Mathur
Super User
3 years ago
Hi,

Write this measure

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

Hope this helps.
- Leh
  New Member
  3 years ago
  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
    Super User
    3 years ago
    You are welcome. That is correct.

Related content