Forum Discussion

ls784's avatar
ls784
Helper I
10 months ago
Solved

Matrix Totals Incorrect

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!

Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    10 months ago

    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

10 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    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's avatar
      ls784
      Helper I
      10 months ago

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

       

      • grazitti_sapna's avatar
        grazitti_sapna
        Super User
        10 months ago

        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)
        )

  • danextian's avatar
    danextian
    Super User
    10 months ago

    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's avatar
      ls784
      Helper I
      10 months ago

      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's avatar
        v-menakakota
        Community Support
        10 months ago

        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.