Forum Discussion

ls784's avatar
ls784
Helper I
9 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 protect...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    9 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

grazitti_sapna's avatar
grazitti_sapna
Super User
9 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
9 months ago

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

 

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    9 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)
    )

    • ls784's avatar
      ls784
      Helper I
      9 months ago

      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's avatar
        grazitti_sapna
        Super User
        9 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