Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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])
Solved! Go to 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)
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)
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]))
)
)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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).
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
94 | |
88 | |
35 | |
32 |
User | Count |
---|---|
153 | |
101 | |
82 | |
63 | |
52 |