Table subtotal is wrong

[Anonymous]

I have a matrix that shows the incident count for the user-selected month(s) and the previous equal number of months.  Its rows are broken down by Week of the Month (as defined below*) and day and includes a grand total.

The problem is the Week of the Month subtotals are wrong if multiple months are selected.  However, the daily values and the grand total compute correctly. 

Details:

I have two calculations (_Inc Count and _Inc Count PMs) which count the number of incidents for the selected months and for the equivalent number of previous months, respectively.  So if I select April and March, _Inc Count PMs returns the counts for Jan and Feb.  This works great at the day level and at the overall level.  But for some reason, at the weekly level, it's very wrong.  

As shown here, the daily PMs values (yellow highlight) are correct and the final total of 67,177 (also in yellow) is correct but the W1 days clearly do not add up to 41,722.

Here is the view collapsed:

I can't figure out why the weekly values do not equal the total of the daily values

* We use a 4-week month where the 4th week of fhe month may have more than 7 days.  Here is the formula for the week number:

__Date Scorecard Week of Month (#) =

IF('DATE Table'[Day] >= 1 && 'DATE Table'[Day] <= 7, "W1",

IF('DATE Table'[Day] >= 8 && 'DATE Table'[Day] <= 14, "W2",

IF('DATE Table'[Day] >= 15 && 'DATE Table'[Day] <= 21, "W3",

IF('DATE Table'[Day] >= 22, "W4"))))

The calulcation for _Inc Count = DISTINCTCOUNT('Incidents'[Inc_Number])

Here is a link to the how I calculate the PMs value:  PMs Calculation

So why is the matrix subtotalling incorrectly????

Please help!

[Anonymous]

Our company has blocked all cloud service uploads so unless there is some other way to provide a file.....

[Anonymous]

Hi, @Anonymous 

Can you provide your original data or pbix file(remove sensitive information) , it may help us to deal with your issue more easier .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.