cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
newbdaxuser
New Member

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

Please try

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

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.

@newbdaxuser 

Then try

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

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors