Hello,
I am new to Power Bi and I need your help to check if it is possible to create in power bi a table from another one which have sales opportunities updated every week (sometimes there is no change in opportunities for weeks).
| ID | Account Name | Opportunity Name | Opportunity Owner | Client Buying Milestones | Probability (%) | Opportunity Size (in K).amount | Close Fiscal Quarter | Qualified Opportunity | FOP Name | Forecast Category | Partners | Date |
My problem is that I need to create a sales funnel but opportunities ID are sometimes reused for some other new opportunity so the id is not really a unique ID and it is impossible to record an opportunity as lost so in excel I combined ID with opportunity name to create a unique ID (UID = ID&OpportunityName). I also combine in sourcing table (Date&ID&OpportunityName).
Then I used this UID to create a pivot table as follow:
| UID | Account Name | Opportunity Name | MIN of Date | MAX of Date |
From pivot table I combine MAX_of_Date&UID and I use it to vlookup in the sourcing table and bring the latest value for every unique opportunity. The resulting table is as follow:
| MAX DATE&UID | UID | Account Name | Opportunity Name | MIN of Date | MAX of Date | Latest Opportunity Size | Latest Client Buyin Milestone | Solution | Proposal | Shortlisted | Negotiation | Won | Loss | Status | Country | Account | Age | Close Fiscal Quarter |
I am sure that there should be millions of way to do it better and more efficient but unfortunately I don't know how could I do it.
If you could help me to figure out how could I do this in power bi I'd appreciate.
I found some posts for creating a table from other, using filter, naturalinnerjoin and summarizecolumns but I have not found the way to do it.
Thanks in advance for your support.
