How can I get the count of records from a filtered matrix table?

[dtbingh]

I have a matrix with 3 rows.. Showing the Company name, the content role function(aka department), and email address. I simply want to have another column that will count the total number of accounts(email addresses) per department. My issues is that every time I do the count just shows the total email addresses, and isn't filtered by the specific companies department I'm looking at. 

This is the table currently: 

I can't click the drop down because there are email addresses, but for example if you look at Armstron Backus & Co. under Audit and Accounting there are 3 email addresses, meaning there are 3 accounts. How do I get the column on the right to show the number 3 next to Audit and Accounting?? Also, have the total number of accounts next to the company name. 

[HotChilli]

This is a classic sign of a missing or incorrect relationship.  Would need to see a model diagram (but would prefer a pbix with test data)

[dtbingh]

Thought I should have included that in the original post. 

Here it is:

[dtbingh]

I actually think I just inched a little closer by changing the cross filter direction to both. 

Now just the total next to company name is wrong

[HotChilli]

I can't tell what fields are included in the visual and have you changed the count field since the first post?

The model doesn't look ideal for reporting purposes.  It looks like ContentRoleLookup... is a dimension table but it's being treated as a fact table.  Contacts is the reverse of that. 

Ultimately it depends what other things are going to be shown in visuals.

If you explain the fields in the visual, I'll see if I can help with that.

[dtbingh]

So I didn't change the count at all, rather I just changed the cross filter direction in the model diagram and that enabled the count for department to total correctly. My issue now is that the count across from company name is adding up incorrectly and is not totalling all the accounts from each department.

Here is the complete report:

I'm not really sure how to go about fixing the underlying table structure, I tried to mimick what I saw in Dynamics CRM

[dtbingh · Accepted solution]

After looking through other forums I tried using the Arcterys Matrix Light V1.0.2 and now the total next to company name is being summed correctly. I'm not sure if this is an issue with Power BI Matrices like it seemed to be in the post I found from 2017, or if the issue is with my underlying model diagram.. 

Anyways, to sum it up incase someone happens by this post. 

1. You'll want to visit: https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot to fix your relationships

2. If the totals are still wrong then try using the aforementioned Matrix available right from the marketplace!