Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Matrix Aggregation Issue

I am having troubles getting my totals to add correctly in my matrix. Here are my DAX measures: 

Expected Hours = IF([WH Affected] = 0 && [Worked Hours] = 0,0, SUM(L_Date[Expected Hours Worked]) * DISTINCTCOUNTNOBLANK(L_EmailsAndNames[Person_ID])) 
WH Affected = SUM('Days Off Requested'[Work Hours affected])
Worked Hours = SUM(Hours[Hours])
 
While this works on an individual level, it does not work on a total level. For example, if someone doesn't start until after x-date, it is still adding expected hours into the final total - even if it shows 0 for that person's line. 
 

 

Any help would be appreciated! Thanks! Here are the tables I'm using: Expected Hours Tables 

3 Replies

  • MohammadLoran25's avatar
    MohammadLoran25
    Solution Sage
    3 years ago

    Hi Anonymous ,

    If you provide us with a sample data it would be great, because I did not get exactly what you mentioned.

    Btw, as it is about a total problem, you need to

     

    1-Use SUMMARIZE Function and Put it in SUMX. Like:

    SUMX (
    SUMMARIZE ( 'YourTable', 'YourTable'[Column1], 'YourTable'[Column2],... ),
    [YourMeasure]
)

     

    If this solves your question, Please give it a thumbs up and accept it as a solution to make it easier for the others to find what they are looking for.

     

    Regards,

    Loran

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I added a link to my data tables in the original post. I tried your solution and got the below: 

       

      • MohammadLoran25's avatar
        MohammadLoran25
        Solution Sage
        3 years ago

        Anonymous ,

        Yes but that link does not contain all your tables to check your measures based on them.

        How did you write the SUMX over SUMMARIZE? It should work.