Forum Discussion
Matrix sum calculating incorrectly
So in the end, the issue seemed to be that if all values are filtered out by the slicer for name, then that row doesn't appear, even if you have rows set to Show items with no data. The issue is that the row is showing cells with no data, but when there's no row to begin with due to filters, you don't get that far. You're not the first one to complain about this functionality.
The workaround I was able create is obnoxious, but it gets the result you want. First, i created a Users table. Do NOT relate it to your X3 Activity table.
Users = SUMMARIZE('X3 Activity', 'X3 Activity'[Comember ID], 'X3 Activity'[Comember Name (First - Last)])Next, I made the slicer use the value for Comember Name from the Users table instead of the Activity table. From there, I created a measure that sums Activity Count 2 while respecting any filters on the Users table:
SumActivityCount2 = CALCULATE(SUM('X3 Activity'[Activity Count 2]),'X3 Activity'[Comember Name (First - Last)] IN VALUES('Users'[Comember Name (First - Last)]))And used that in place of Activity Count 2 in the matrix visual.
You may be able to avoid creating the Users table if you instead create a table for Item Type that doesnt' also include info from RAG Targets. The many to many relationship between Activity Reference and X3 Activity was a monster to try and navigate. I attempted this, but had the issue where it would show too many rows (like a Candidate Email Sent row for Perm & Temp categories).
I'm sure there's a cleaner or more elegant way to handle the situation, but this is what I found worked. https://drive.google.com/open?id=1HdiqBjcQ0rZoUmB9Hl0Yv48uRc7wt_mf
Hi
That was easier than I thought it might be, this link should hopefully take you to the sample .pbix file
https://app.box.com/s/q2xfdgbj969bof8xhgynezhd6d9otakv
I've stipped it down to just include this issue and a small data set. The top left chart is the one where the blank rows show but the sums don't work correctly, the bottom right has the right sum but the blank rows missing.
Thanks again
So in the end, the issue seemed to be that if all values are filtered out by the slicer for name, then that row doesn't appear, even if you have rows set to Show items with no data. The issue is that the row is showing cells with no data, but when there's no row to begin with due to filters, you don't get that far. You're not the first one to complain about this functionality.
The workaround I was able create is obnoxious, but it gets the result you want. First, i created a Users table. Do NOT relate it to your X3 Activity table.
Users = SUMMARIZE('X3 Activity', 'X3 Activity'[Comember ID], 'X3 Activity'[Comember Name (First - Last)])Next, I made the slicer use the value for Comember Name from the Users table instead of the Activity table. From there, I created a measure that sums Activity Count 2 while respecting any filters on the Users table:
SumActivityCount2 = CALCULATE(SUM('X3 Activity'[Activity Count 2]),'X3 Activity'[Comember Name (First - Last)] IN VALUES('Users'[Comember Name (First - Last)]))And used that in place of Activity Count 2 in the matrix visual.
You may be able to avoid creating the Users table if you instead create a table for Item Type that doesnt' also include info from RAG Targets. The many to many relationship between Activity Reference and X3 Activity was a monster to try and navigate. I attempted this, but had the issue where it would show too many rows (like a Candidate Email Sent row for Perm & Temp categories).
I'm sure there's a cleaner or more elegant way to handle the situation, but this is what I found worked. https://drive.google.com/open?id=1HdiqBjcQ0rZoUmB9Hl0Yv48uRc7wt_mf