Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to display Grand Total correctly with USERELATIONSHIP?

Good day, I’m having trouble with getting the correct Grand Total for my pivot table.   I have two tables. Fact table of email worked by an employee. It has a pkey column which is just a con...
"DAX"
"Grand Total"
"Power Pivot"
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

Please try

calcEmails :=
SUMX (
VALUES ( 'Fact'[Fact.agentId] ),
COALESCE (
[sumEmails],
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
)
)
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hi tamerj1 , thanks for the input. i am using excel. and it seems coalesce is not available.

    Failed to resolve name 'COALESCE'. It is not a valid table, variable, or function name.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 

      Then try

      calcEmails :=
      SUMX (
      VALUES ( 'Fact'[Fact.agentId] ),
      VAR SumEmails = [sumEmails]
      RETURN
      IF (
      ISBLANK ( sumEmails ),
      CALCULATE (
      [sumEmails],
      USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
      ),
      sumEmails
      )
      )

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        tamerj1 , i tried the revised measure. however, it only shows the data for week 6/4/23. it does not show the data of ceb1ccabalquinto(ntloginKey) on week 6/11/23.