cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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])`

1 ACCEPTED SOLUTION
Helper II

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)

3 REPLIES 3
Helper II

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)

Community Champion

Hi @RevOps608 ,

``````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]))
)
)``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors