Forum Discussion
Matrix sum calculating incorrectly
Hi
Newbie to PowerBI here so apologies if this is an easy question, but I can't find a solution that works for me.
I have the following matrix in my report, which is summing activities performed by Period and by Week (we operate on a lunar calendar with 13 x 4 week periods) within either the Perm category, the Temp category, or the candidate engagement email and visit attended actions can also exist without either category.
What I want is a matrix which sums these by week, including showing blank rows / weeks to help highlight gaps.
I've managed to achieve this, except that where an activity exists more than once in different categories, each row shows the total. E.g. in Period 10 Week 4, there are 3 Candidate Engagement Email Sent in total, but the matrix shows 3 in the blank category, the Perm category and the Temp category.
I have used the same logic to create the multi level rows as the multi level columns - the columns seem to work correctly where as the rows do not.
My value is a straight sum, no DAX involved, and I am using 3 tables with single direction relationships.
The Columns (Period, Week) come from my Period Reference table.
The Rows (Item Type, XMS Activity Type Name) come from the Activity Reference table.
The Value (Activity Count 2) comes from the X3 Activity table
If I change the rows so they come from the X3 Activity table along with the Value, then sums work correctly but I lose the blank rows.
Any help would be much appreciated
Thanks
Geoff
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
9 Replies
Your second solution should be the one you work with here.
There's an option you can set in the visual options pane (where you set things in row, column and value buckets) to "Show items with no data". I believe you want to click the down arrow on your "XMS Activity Type Name" row and select the option there.
If that doesn't give the visual you want, try different combinations of selecting this option for other fields. Let us know if you make any progress!Hi
Thanks for the quick reply
I have given that a go and hasn't worked unfortunately. The blank rows don't come back.
I had attempted that in one of the many hours I have been trying to sort this, but tried it again to double check but no joy. Selecting that option actually doesn't seem to have any impact on the matrix at all :/
Geoff
Could you share the code you're using for [Activity Count 2] with us? It looks like you may have used a filter removing function incorrectly.
