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