Forum Discussion
Helper IIIMatrix Total does not filter, does not reflect what it is totaling.
I went from one weird situation to another.
As you can see in the screenshot, while the individual rows are accurate, the total does not represent the total. It is as simple as that. I don't see how this is anything I have control over.
MonthlySalesGoal is not even a measure, just a column in a table.
This total is valid for all TerritoryNames, but obviously, I am filtering it out to 5 of them, and the totals should reflect that.
Phil
I found the issue with your matrix.
It was not to do with the measures but rather with the way the relationships were setup.
You were using the Users table and a value from the MonthlyGoals table, but they were not linked together correctly. So due to this relationship not being in place the total being shown was correct based on the filter context (because there was no filtering being applied between the Users table and the MonthlyGoals table)
So what I did to resolve this was to change the Cross Filter direction on the Customer table to the Territory Table to be Both.
And now the total displays correctly.
I also put in another measure called Correct Total if you were wanting to have a different measure based on the Row Context.
You can find the uploaded file here: https://1drv.ms/u/s!Apxn-69XhcAmhqhy5yutRFAH92ID6Q
9 Replies
- GilbertQ
Super User
I have come across a similar situation and it was where my subtotals did not add up to the rows in the column.
The reason for my issue was because the filter context of my measure that I created worked fine in the row context, but at a subtotal level there is no row context, so the measure appears to display the incorrect results.
I read this great blog post by the people from Power Pivot Pro, where they explains why it happens and how to resolve the issue.
Subtotals and Grand Totals That Add Up “Correctly”
I am sure that it can solve your issue, if you get stuck or it does not make sense let us know.
psmith-nhs-incI was able to cut out almost everything, and still get it to screw up, so here is the cut down PBIX. I do not think the the link suggested applies, at least not in terms of dates. Link to PBIX below the picture.
Thanx to anyone who can take a look.
https://dl.dropboxusercontent.com/u/9936354/SalesRepDEVpms4-17-17xxx.pbix
- GilbertQ
I found the issue with your matrix.
It was not to do with the measures but rather with the way the relationships were setup.
You were using the Users table and a value from the MonthlyGoals table, but they were not linked together correctly. So due to this relationship not being in place the total being shown was correct based on the filter context (because there was no filtering being applied between the Users table and the MonthlyGoals table)
So what I did to resolve this was to change the Cross Filter direction on the Customer table to the Territory Table to be Both.
And now the total displays correctly.
I also put in another measure called Correct Total if you were wanting to have a different measure based on the Row Context.
You can find the uploaded file here: https://1drv.ms/u/s!Apxn-69XhcAmhqhy5yutRFAH92ID6Q