Matrix Totals Incorrect if column is in a related table

[vdog01]

Hi I'm having a problem getting the matrix totals to sum up correctly for a column that is in a related table.

I have the following 2 tables t1 and t2 that are related as follows

The data are as follows

When I create a Matrix with the following hierarchy (Level 1 from T1, FixedBy from T2 and Name from T1)

The total sum of hours seems to be the total for the 3rd level (ie Name) instead of the 2nd level (ie FixedBy)

Any suggestions on how to make this work?

[v-juanli-msft]

Hi

Does this meet your requirement?

 

To get results above, I edit the cross filter direction of two tables as Both.

 

Best Regards

Maggie

[vdog01]

Hi Maggie,

Thanks for the reply, the matrix still shows some incorrect values unfortunately

[v-juanli-msft]

Hi

Based on my understanding, the field "Sum of Hours" in the matrix should show values as the column "Hours" in Sheet1, so the value for "AD" OF "Sum of Hours" is 10 instead of 5, Is my understanding right?

Best Regards

Maggie

[vdog01]

Hi Maggie,

Yes, that's correct.  In the raw data for T1, AD only has 5 hours