Getting Sum of value based on one or both columns without duplicates

[RevOps608]

I need to get total sales (Total Sales ACV) from the Opportunity Table for Accounts in the Accounts table using two relationships to the same table without duplicating sums. The Acct Id can be the in the Acct ID column in the Opportunity Table, the CPA Acct in the Opportunity Table or both. I want to get the total sales without adding the sales twice if they're in both fields.

The two relationships are Account(AccountId) > Opportunity(AcctId) &

Account(AcctId) > Opportunity(CPA_Acct_Id). I've tried using Gregs formula from a previous question posted without any luck.

Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])

[RevOps608 · Accepted solution]

I was able to solve this. Greg's measure did end up working. I just had to get my table in proper format.

Measure = SUMX(
SUMMARIZE(
FILTER('2024 End Client CPA Calc Table',
'2024 End Client CPA Calc Table'[ACV Yr] = 2024),
'2024 End Client CPA Calc Table'[ Opp Id],
"ACV", AVERAGE('2024 End Client CPA Calc Table'[ACV])),
[ACV])

Summary table

VAR endclient24 =
SUMMARIZE(
FILTER(
FILTER(
Opportunity,
Opportunity[AccountId] <> [Blank]),
Opportunity[Close_Year__c] = 2024
),
Opportunity[ Opp Id],
Opportunity[AccountId],
"ACV Yr", Opportunity[Opp Close Year],
"ACV", SUM(Opportunity[Total Sales ACV])
)
VAR cpaacct24 =
SUMMARIZE(
FILTER(
FILTER(
Opportunity,
Opportunity[CPA_Contact_Account__c] <> [Blank]),
Opportunity[Close_Year__c] = 2024
),
Opportunity[ Opp Id],
Opportunity[CPA_Contact_Account__c],
"ACV Yr", Opportunity[Opp Close Year],
"ACV", SUM(Opportunity[Total Sales ACV])
)
VAR endclient =
SUMMARIZE(
FILTER(
FILTER(
FILTER(
Opportunity,
Opportunity[AccountId] <> [Blank] ),
Opportunity[Close_Year__c] = 2023 ),
Opportunity[Record_Type_Name__c (groups)] <> "ERC"
),
Opportunity[ Opp Id],
Opportunity[AccountId],
"ACV Yr", Opportunity[Opp Close Year],
"ACV", SUM(Opportunity[Total Sales ACV])
)
VAR cpaacct =
SUMMARIZE(
FILTER(
FILTER(
FILTER(
Opportunity,
Opportunity[AccountId] <> [Blank] ),
Opportunity[Close_Year__c] = 2023 ),
Opportunity[Record_Type_Name__c (groups)] <> "ERC"
),
Opportunity[ Opp Id],
Opportunity[CPA_Contact_Account__c],
"ACV Yr", Opportunity[Opp Close Year],
"ACV", SUM(Opportunity[Total Sales ACV])
)

RETURN
UNION(
endclient24,
cpaacct24,
endclient,
cpaacct)

[Samarth_18]

Hi @RevOps608 ,

Please try the below code:-

Total Sales ACV = 
CALCULATE(
    SUM(Opportunity[SalesAmount]),
    UNION(
        FILTER(Opportunity, Opportunity[AcctId] IN VALUES(Accounts[AccountId])),
        FILTER(Opportunity, Opportunity[CPA_Acct_Id] IN VALUES(Accounts[AcctId]))
    )
)

[RevOps608]

Thank you for replying. Your measure is on the right in the values, 'End Client - CPA Acct Total Sales'. It's only returning sales from the 'Acct Id' field. The measure on the left in the matrix, ('Total Sales (ACV)' is a measure that sums sales from the 'Acct Id' field in the opportunity table. The middle measure '2024 Opp CPA Acct ACV' is the sum of sales from 'CPA Acct Id'.

Side note: The active relationship between the Account and Opportuntiy table is 'Acctont[Acct Id] > Opportunity[Acct Id]. There is an inactive relationship between Account[Acct Id] > Opportunity[CPA_Contact_acct] (aka cpa contact Id).