Matrix sum calculating incorrectly

[Geoff_Clark]

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

[Cmcmahan]

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!

[Geoff_Clark]

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

[Cmcmahan]

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.

[Geoff_Clark]

Hi

No code I'm afraid, this is just straight out of the data table (which comes out of the data warehouse showing count of activities by person by week)

I have previously also tried using this measure which I found in another topic, to force zeros instead of blanks, but that didn't work either:

You may be on to something though perhaps. I used the Summarize function to create my Activity Reference table, from which I managed to show the blank items. Potentially that could be what is causing the sum to fail, but then I need another way to show the blanks...

Thanks again

[Cmcmahan]

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.

[Geoff_Clark]

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

[Cmcmahan]

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.

[Geoff_Clark]

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

[Cmcmahan · Accepted solution]

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