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
Yeah, there's definitely something weird going on. It might be because you're using many to many relationships between all your tables. I would assume that both your reference tables would be on the one side of a one-to-many relationship, but I don't know your data.
Is it possible for you to share even a small sample of your data here? A copy of your .pbix file would be the most helpful. If that's not possible, some anonymized sample data for 2 or 3 periods/activities that we could copy/paste would be very helpful.
Hi
I've put together a small, anonymised sample version, then realised that there is no attach function on the forum (which is quite frustrating) and our systems block me from accessing any file transfer sites... :/
Will try and find a way around or to sweet talk our IT team, but any other ideas in the meantime would be welcome
G
Yeah, if you can't even upload to Google Drive and share there, that's an annoyance (though understandable from the data security standpoint).
If the tables are small enough, you could literally copy/paste them in this thread. For example, here's a small reference table from one of my projects:
Category Rank Description Software 1 Office 365, etc. Peripherals 2 Printers, phones, and other devices Workspace 3 Docking stations monitors, etc. Network 4 Internal or external network issues User Account 5 Staff logins, passwords and application authorizations Security 6 Suspicious emails and websites 7 No Category Basically anything that we can copy/paste into a blank PowerBI report for testing is useful. Trying to hand-transcribe 30 rows of data with 10 columns each gets really annoying very quickly, but happens more often than not because people just screenshot entire tables from the PBI interface.
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