Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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

Sample 

 

5 REPLIES 5
tamerj1
Super User
Super User

Hi @Anonymous 

Please try

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

Anonymous
Not applicable

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.

@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
Not applicable

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

@Anonymous 

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]

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.