Forum Discussion
Calculate Weighted Pipeline between two tables
Hello!
I'm stumped on how to create a weighted pipeline column in Power BI and am hoping someone can help. In Excel it's quite simple since everything is together in one table...In Excel to get Weighted Pipeline I'm multiplying the Extended Amount Base (Total amount) * 1.0 if the Deal Status is Commit, *.7 if the Deal Status is Likely, *.4 if the Deal Status is Best Case, and *0 if the Deal Status is Not Forecasting.
In Power BI however, the Deal Status and Extended Amount Base columns are in different tables...and those tables already are connected by an active relationship that cannot be removed, so I cannot figure out how to run this measure/calculation and/or create another relationship between the two...
I need to calculate a new Weighted Pipeline column to showcase all of the weighted elogic_extended_amount_base amounts (from the Historical Forecast Appended table) based on the elogic_deal_status_display data from the Historical Opportunities Appended table. Unsure how to connect a new relationship between the two tables while keeping the existing one intact. Also unsure what Measure to write to multiply elogic_extended_amount_base based on the populated data field for the elogic_deal_status_display column being Commit, Likely, Best Case, or Not Forecasting.
- elogic_deal_status_display column showcasing Commit in the Historical Opportunities Appended Table would multiply the elogic_extended_amount_base (Historical Forecast Appended Table) * 1.0 to get the weighted pipeline Commit amount.
- elogic_deal_status_display column showcasing Likely in the Historical Opportunities Appended Table would multiply the elogic_extended_amount_base (Historical Forecast Appended Table) * .7 to get the weighted pipeline Likely amount.
- elogic_deal_status_display column showcasing Best Case in the Historical Opportunities Appended Table would multiply the elogic_extended_amount_base (Historical Forecast Appended Table) *.4 to get the weighted pipeline Best Case amount.
- elogic_deal_status_display column showcasing Not Forecasting in the Historical Opportunities Appended table would multiply the elogic_extended_amount_base (Historical Forecast Appended) table * 0 to get the weighted pipeline Not Forecasting amount.
Example of chart created from pivot table in Excel that I'm trying to replicate in Power BI (that includes the Weighted Pipeline).
Any help would be greatly appreciated!!! Thanks in advance!
3 Replies
- lbendlin
Super User
please explain this part
"Deal Status and Extended Amount Base columns are in different tables...and those tables already are connected by an active relationship that cannot be removed"
in more (much more) detail.
Deal Status is a column with thousands of rows of populated data (Commit, Best Case, Likely, Not Forescasting) on the Historical Opportunities Appended table. Extended Amount Base is a column with thousands of rows of populated data ($ amounts) on the Historical Forecast Appended table.
The Historical Opportunities Appended and Historical Forecast Appended tables are already connected in the Model by "Opportunity" and "Opportunity ID" as an active relationship.
Trying to calculate new "Weighted Pipeline" amounts based on the parameters mentioned in the inital message. Do you know what measure I could write in that would allow me to conditionally multiply the extended amount base $ amount based on the related deal status on the other table?
Thanks for your response lbendlin
- lbendlin
Do you hav snapshots of deal status and Extended Amount base or do you have a list of change events (from the Opportunity Field History object, for example)?
I am asking this because I did a similar report but I constructed my data points outside of Power BI (in a SQL Server scalar function) based on the SFDC events data.
