How to display Grand Total correctly with USERELATIONSHIP?

[newbdaxuser]

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 concatenation of week and agentId.
• Dimension table that has the login data of employee. It has an active citrixKey connected to Fact table.In general, Fact.agentId is the same as Dimension.citrixUsername, however an employee encountered issues with the citrixUsername and is now using Dimension.ntLogin. When I tried to build a pivot table with rows as agentId and values as sum of values, the grand total is not correct.
  
  Here are my measures:
  
  sumEmails:=
  SUM( 'Fact'[Value] )
  calcEmails:=
  IF( 
  [sumEmails] = BLANK(),
  CALCULATE(
  [sumEmails],
  USERELATIONSHIP('Fact'[pkey], 'Dimension'[ntloginKey])
  ),
  [sumEmails]
  )
  
  
  

Sample 

[tamerj1]

Hi @newbdaxuser 

Please try

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

[newbdaxuser]

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]

@newbdaxuser 

Then try

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

[newbdaxuser]

@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.

[tamerj1]

@newbdaxuser 

You may try something complex like

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

or could something just simple as

calcEmails :=
CALCULATE (
[sumEmails],
USERELATIONSHIP ( 'Fact'[pkey], 'Dimension'[ntloginKey] )
)
+ [sumEmails]