Matrix Totals Incorrect

[ls784]

Hello,

I'm really hoping someone can help me. I'm struggling with a data set and getting a matrix visual totals to display what I need.

I have 3 tables as below (simplified example for data protection). I can’t change my data too drastically, it’s what I’ve been given.

Data:

Relationships:

I'm trying to create a matrix that looks like this with the count of calls for each group, however because Users are in multiple groups/subgroups it's double counting their call count.

Therefore I have tried to use a count of how many groups they are in and divide their call count by that, this works for individual lines but the totals and % GT don’t add up.

CallCount =

VAR countcalls = CALCULATE(DISTINCTCOUNT(Calls[CallID]))

RETURN

if ( ISINSCOPE( UserGroups[SubGroup]),

DIVIDE( countcalls , CALCULATE( MAX(UserGroups[Count]), ALLEXCEPT( UserGroups, UserGroups[User])), 0),

countcalls )

Any help would be appreciated!

[danextian]

Hi @ls784 

What is your expected result, visually, using your sample data? What numbers do you expect to see? If you could  provide that then it would be eaiser for us to come up with a better solution. 

[ls784]

Hi
I *think* this is what I'm expecting to see. I'm not sure if it is possible? I just need the data to make sense as currently it doesn't!

[v-menakakota]

Hi  @ls784  ,

Thanks for reaching out to the Microsoft fabric community forum.

Please go through the pbix which i shared.

Regards,
Microsoft Fabric Community Support Team.

[v-menakakota]

Hi @ls784 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @ls784 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[grazitti_sapna]

Hi @ls784,

Try below DAX

CallCount =
VAR UsersTable =
VALUES(UserGroups[User])
RETURN
SUMX(
UsersTable,
VAR UserCallCount =
CALCULATE(DISTINCTCOUNT(Calls[CallID]), Calls[User] = UserGroups[User])
VAR GroupCount =
CALCULATE(DISTINCTCOUNT(UserGroups[SubGroup]), UserGroups[User] = UserGroups[User])
RETURN DIVIDE(UserCallCount, GroupCount, 0)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[ls784]

Thank you for your help.
Unfortunately I got an error with this code:

[grazitti_sapna]

Ok, got it @ls784 

Try this 

CallCount =
VAR UsersTable = VALUES(UserGroups[User])
RETURN
SUMX(
UsersTable,
VAR CurrentUser = [User] -- store current user in variable
VAR UserCallCount =
CALCULATE(
DISTINCTCOUNT(Calls[CallID]),
FILTER(Calls, Calls[User] = CurrentUser)
)
VAR GroupCount =
CALCULATE(
DISTINCTCOUNT(UserGroups[SubGroup]),
FILTER(UserGroups, UserGroups[User] = CurrentUser)
)
RETURN DIVIDE(UserCallCount, GroupCount, 0)
)

[ls784]

Unfortunately this doesn't quite give me what I want, the totals don't add up because we can see it's counting the duplicates

[grazitti_sapna · Accepted solution]

Hi @ls784,

Please try this

CallCount =
VAR UniqueUsers =
SUMMARIZE(
UserGroups,
UserGroups[User] -- distinct list of users in current visual context
)
RETURN
SUMX(
UniqueUsers,
VAR CurrentUser = UserGroups[User]
VAR UserCallCount =
CALCULATE(
DISTINCTCOUNT(Calls[CallID]),
FILTER(Calls, Calls[User] = CurrentUser)
)
VAR GroupCount =
CALCULATE(
DISTINCTCOUNT(UserGroups[SubGroup]),
FILTER(UserGroups, UserGroups[User] = CurrentUser)
)
RETURN DIVIDE(UserCallCount, GroupCount, 0)
)

if it doesn't work, please share sample data