Calculating Won Revenue Per Account - Multiple Accounts Involved in Opportunity

[Gadgetstar]

Hi, This question is stumping me and I'd really appreciate some guidance.

We have Dynamics CE as our CRM and I am using Power BI to create dashboards. 

We have the possibility of multiple accounts being involved in an Opportunity. The Reseller, (VAR)  The integration partner (Partner) and the End User (Account). Before we win the project there may be multiple Partners involved in the bidding of the opportunity as they compete to implement the project. 

We have 3 tables in this relationship. The Account table which obviously holds the Accounts info for every Account (there is a field that identifies if they are typically a VAR, Partner or End User). The Opportunity table, and a table called OpportunityPartners which apart from a unique identifier has an Account ID field and an Opportunity ID field. So for 1 opportunity there could be 3 or 4 records in the Opportunity Partner table pertaining to the Opportunity.

I am trying to create a Measure (or Column) , "Account_Won_Revenue", that will calculate the Won Revenue that each Account has been involved in. For example, if I had one won Opportunity for $10k that had VAR1, Partner 1, and End User 1 involved, then $10k would be recorded on each Account record.

The Account table is related to the Opportunity Partner table and Opportunity is related to the Opportunity Partner table.  There are also other relationships between Accounts and Opportunity directly as some fields are fixed as they are always required on the Opportunity (for example Ship to Account). 

I tried to use:

Account Won Value =

    CALCULATE(

        SUM(Opportunity[Actual Revenue]),USERELATIONSHIP(Account[Account],opportunitypartner[account]))+0

Interestingly this calculates the won revenue for the End User account, but not for the VAR or the Partner(s) involved in the Opportunity. 

When I filter the Opportunity Partner table in Power BI for a single opportunity that I know has multiple partners, I can see the multiple records (as expected), so I know the join table has the correct data stored. 

Any help on what I am missing either in the DAX measure or the relationship would be really helpful. 

[v-dineshya · Accepted solution]

Hi @Gadgetstar ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @FBergamaschi , Thank you for your prompt response.

Hi @Gadgetstar , As mentioned by @FBergamaschi , Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,
Dinesh

[Gadgetstar]

Hi Diniesh and FBeramaschi,

Thanks for your quick response, Your request is easier said than done as my current Power BI config is against our complete CRM system with thousands of records which also includes confidential information. 

I have since managed to figure out the issue.

One Account can be related to Many OpportunityPartner records. Each Opportunity can be related to Many OpportunityPartner records, however there would normally only be One OpportunityPartner record relating the Account to the Opportunity. 

The Relationship Model:

Account[AccountID] - OpportunityPartner[InvolvedAccountID] (one to many) - Active

Opportunity[OpportunityID] - OpportunityPartner[OpportunityID] (one to many) - Inactive

make sure there is no active relationship directly between Account and Opportunity

Account Table

AccountID	Account Name	Won Revenue (this is the field I am trying to calculate)
1234	VAR1	$12,000
1235	EndUser1	$10,000
1236	Partner2	$10,000
1240	Partner1	$2,000

Opportunity Table

OpportunityID	Opportunity Name	Actual Revenue
Op123	Test Opp	$10,000
Op130	Water Opp	$2,000

OpportunityPartner Table

OpportunityPartnerID	InvolvedAccountID	OpportunityID
OP123	1234	Op123
OP124	1235	Op123
OP125	1236	Op123
OP126	1234	Op130
OP127	1240	Op130

Updated the DAX to match the example tables above:

Won Revenue]=

    CALCULATE(

        SUM(Opportunity[Actual Revenue]),  USERELATIONSHIP(OpportunityPartner[InvolvedAccountID],Opportunity[OpportunityID]))+0

[FBergamaschi]

We can centrainly help you but

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI