Forum Discussion
Calculating Won Revenue Per Account - Multiple Accounts Involved in Opportunity
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 =
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
3 Replies
- FBergamaschi
Super User
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
v-dineshyaCommunity Support
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,
DineshHi 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
