Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good day,
I’m having trouble with getting the correct Grand Total for my pivot table.
I have two tables.
Hi @Anonymous
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.
@Anonymous
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.
@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]
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |