My first post at Community!
I’m dealing with a tricky dataset that revolves around deals flowing from our CRM system. Some of the deals are split between two teams. In some cases there is a team name mentioned on one of the records of a certain split deal, but not on the other. Every deal has revenue associated with it, however not all revenue is ‘impacted’ by the deal team (depends on the activities they take, for the purposes of this example the data is already provided). When a deal is impacted the revenue counts as ‘Revenue - Team Impact’. When a deal is not impacted it does not count (zero or Null value under ‘Revenue - Team Impact’). Split deals provide an immense challenge since the entire revenue needs to be assigned to the deal team if impact is positive. In addition the deal team should be named on both split records (Again, if there is an impact). In the past I used to do this manually looking at Excel reports. However now the data is fed directly to Tableau and I’m not able to fully automate these actions.
I’ve attached a spreadsheet with dummy data to better illustrate my challenge. There are two tabs. In ‘What it is’ you can see how the data looks coming from the CRM. In ‘What it Should Be’ tab you can see the changes I’m trying to apply with calculated fields (highlighted).
What I’ve done so far:
- In most cases I was able to match each split record with its counterpart using a calculated field with a lookup function. Some deal records occupy two rows which puts a stick in my lookup formula.
- Separated the split % from the deal name as its own field
- Created a simple calc for Revenue - Team Impact that does not take split deals into account
Where I need help - applying the following logic:
- If a team appears on one split record and impact is positive, and the other record has no impact and no team name then the team name should appear on the other split record.
- Impact for that second record should be changed from no impact to ‘impact’.
- Revenue - Team Impact should be changed to the appropriate amount based on the split %.
- If two split records have different team names and both have impact do nothing.
- If two split records have same team name and both have impact do nothing.
- If one record has a team name but no impact, do nothing.
I see this logic implemented into three fields: Deal Team Name, Deal Team Impact, Revenue - Team Impact
However anything else that works is welcomed.
Nicholas Abbott - Saw your post from a couple of years ago about the same topic. Where you able to figure out an answer?
Thank you for your patience if you made it this far!
Appreciate any advice.
Split Opp Dummy Data.twbx 105.9 KB