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

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