topic Matrix Aggregation Issue in DAX Commands and Tips

Matrix Aggregation Issue

Anonymous — Mon, 10 Apr 2023 16:37:15 GMT

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

Re: Matrix Aggregation Issue

MohammadLoran25 — Mon, 10 Apr 2023 16:25:50 GMT

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

Re: Matrix Aggregation Issue

Anonymous — Mon, 10 Apr 2023 16:40:26 GMT

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

Re: Matrix Aggregation Issue

MohammadLoran25 — Mon, 10 Apr 2023 16:50:48 GMT

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.