Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Good day,
I’m having trouble with getting the correct Grand Total for my pivot table.
I have two tables.
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.
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.
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]
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.