Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
texmexdragon2
Helper V
Helper V

Historical CRM table....determining what the stage of an opportunity was when it converted

Hello Community  -  Our SF opportunity history table looks like the following.   We can see the progress of two distinct opportunities...both of which ended converting to a win  (100% - Closed).   And we can see that the probability stage it was at just prior to converting to 100% (i.e. closed won) was 80%.   

 

Of course not all opportunities convert at 80%.   Some can convert at 60, 70, etc.    No matter what, whatever the probability stage was just before the max createddate is the value we are looking for.   Our goal is to be able to report the frequency of "won" conversions by each particular stage (probability).   So the number of "Won" opportunities that converted at 30%....the number that converted at 40%, etc etc.   Any help is appreciated!

 

texmexdragon2_0-1663978964175.png      

 

1 ACCEPTED SOLUTION

Hi,

Download the revised PBI file from here.  I have pasted the additional rows below the rows which i had in the table of the V2 file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23
Ashish_Mathur
Super User
Super User

Hi,

Could you share a slightly larger dataset (in a format that can be pasted in an MS Excel file) and show the expected result on that dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur   Absolutely, thanks Ashish.  

 

Below is a sampling of the data.   The expected result would be to create a table, probably a matrix, that had two columns:   Closed Won    Closed Lost  

 

On the rows of that table would be the different probabilities (stages), such as 30%, 40%, etc.    And the values would be the frequency that each of the stages occured, for the respective columns.   Ideally, you could also use the output values in a histogram or some other visual.   Again, the main objective is to show what the frequency of conversion is to either closed won, or closed lost, per stage.   

 

I would also add that there is one nuance to the data.   Here is a sample.   Where the 0% (representative of a a closed lost opportunity) shows up in two rows.   This is likely because there was some other change made by whoever input the updated record that got captured.   It seems to only occur with opportunities that have been converted to closed lost (0%) and does not always occur.  

texmexdragon2_0-1664031015628.png

 

 

 

OpportunityIdCreatedDateProbabilityForecastCategory
0065e00000ClR4iAAF10/1/2021 22:1680%Pipeline
0065e00000ClR4iAAF10/2/2021 23:0180%Pipeline
0065e00000ClR4iAAF10/5/2021 17:4480%Pipeline
0065e00000ClR4iAAF12/13/2021 23:0580%Pipeline
0065e00000ClR4iAAF12/13/2021 23:1680%Pipeline
0065e00000ClR4iAAF1/13/2022 20:340%Omitted
0065e00000ClQhEAAV10/1/2021 22:16100%Closed
0065e00000ClQhEAAV10/2/2021 22:59100%Closed
0065e00000ClkHVAAZ10/5/2021 14:5030%Pipeline
0065e00000ClkHVAAZ10/5/2021 14:5030%Pipeline
0065e00000ClkHVAAZ12/21/2021 18:4830%Pipeline
0065e00000ClkHVAAZ3/14/2022 0:520%Omitted
0065e00000Clk3EAAR10/5/2021 12:4540%Pipeline
0065e00000Clk3EAAR10/5/2021 12:4540%Pipeline
0065e00000Clk3EAAR10/5/2021 12:5340%Pipeline
0065e00000Clk3EAAR10/5/2021 13:0950%Pipeline
0065e00000Clk3EAAR10/15/2021 15:5850%Pipeline
0065e00000Clk3EAAR10/15/2021 16:0150%Pipeline
0065e00000Clk3EAAR10/18/2021 18:0450%Pipeline
0065e00000Clk3EAAR10/21/2021 18:3350%Pipeline
0065e00000Clk3EAAR10/27/2021 21:1280%Pipeline
0065e00000Clk3EAAR10/27/2021 21:2280%Pipeline
0065e00000Clk3EAAR10/29/2021 14:2680%Pipeline
0065e00000Clk3EAAR11/11/2021 19:18100%Closed
0065e00000CliqTAAR10/5/2021 0:2330%Pipeline
0065e00000CliqTAAR10/5/2021 0:2330%Pipeline
0065e00000CliqTAAR12/21/2021 17:2130%Pipeline
0065e00000CliqTAAR1/14/2022 2:2830%Pipeline
0065e00000CliqTAAR1/14/2022 2:2920%Pipeline
0065e00000CliqTAAR1/14/2022 2:3840%Pipeline
0065e00000CliqTAAR1/14/2022 2:3830%Pipeline
0065e00000CliqTAAR2/3/2022 18:1130%Pipeline
0065e00000CliqTAAR8/5/2022 14:5730%Pipeline
0065e00000CliqTAAR8/22/2022 8:0030%Pipeline
0065e00000ClhQsAAJ10/4/2021 17:3530%Pipeline
0065e00000ClhQsAAJ10/4/2021 17:3530%Pipeline
0065e00000ClhQsAAJ10/6/2021 14:0730%Pipeline
0065e00000ClhQsAAJ10/26/2021 13:0330%Pipeline
0065e00000ClhQsAAJ10/26/2021 14:1140%Pipeline
0065e00000ClhQsAAJ10/26/2021 14:1150%Pipeline
0065e00000ClhQsAAJ10/26/2021 14:1260%Pipeline
0065e00000ClhQsAAJ10/26/2021 14:1270%Pipeline
0065e00000ClhQsAAJ11/30/2021 17:0370%Pipeline
0065e00000ClhQsAAJ11/30/2021 17:0670%Pipeline
0065e00000ClhQsAAJ11/30/2021 17:0670%Pipeline
0065e00000ClhQsAAJ12/6/2021 15:0570%Pipeline
0065e00000ClhQsAAJ12/6/2021 15:0570%Pipeline
0065e00000ClhQsAAJ1/10/2022 20:2470%Pipeline
0065e00000ClhQsAAJ1/10/2022 20:2470%Pipeline
0065e00000ClhQsAAJ1/22/2022 18:2370%Pipeline
0065e00000ClhQsAAJ1/24/2022 17:0170%Pipeline
0065e00000ClhQsAAJ2/14/2022 19:3370%Pipeline
0065e00000ClhQsAAJ2/14/2022 19:3970%Pipeline
0065e00000ClhQsAAJ2/14/2022 19:4670%Pipeline
0065e00000ClhQsAAJ2/14/2022 19:5270%Pipeline
0065e00000ClhQsAAJ2/14/2022 19:5580%Pipeline
0065e00000ClhQsAAJ2/22/2022 20:4380%Pipeline
0065e00000ClhQsAAJ2/22/2022 21:05100%Closed

Hi,

i do not know how much i can help but i would like to try.  If possible, can up enter this data in a workbook and in another tab of that workbook, show the result you want with the help of Excel formulas.  If it is not possible to write the formulas because of complexity, then show the expected figures you want in the other worksheets with an explantion.  I will try to convert those formulas/that logic that into DAX formulas. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur     Hi Ashish  - 

We have Probability from 10 to 80%.   0% indicates closed-lost.    100% indicates closed-won.  

 

A simple example of the output might be (as a matrix):

                                Closed-Lost     Closed-Won

Probability

60%                               11                      05   

70%                               09                      07

80%                               13                      15

 

The above is just saying that 13 opportunities converted to closed-lost at 80%, and 15 converted to closed-won.   

 

texmexdragon2_0-1664122714592.png

Using above as an example, if I were to write the formula logic in plain English, it would be something like:    For each Opportunity ID, on the Max row of "createddate" , if the Probability % = 100, then return the Probability % of the prior row.   (in theory, that prior row is the probability % the opportunity was at when it was converted to closed-won).    Then do the same thing for closed-lost.   The only nuance to this is that sometimes 0% shows up in two rows, so you'd need to have logic that says if the prior row probability % is 0%...AND the prior row is also 0%, then use the next prior row.   

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur    This is what I love about the Power Bi community!    It is night time here in the USA but I will have a look at this first thing in the morning and get back to you.   From an initial look at the Power Bi file it looks good.....just curiuos if you were able to account for those situations where the last 0% is sometimes preceeded by another 0%?   

@Ashish_Mathur    Also, just FYI that "omitted"  =   closed-lost

Hi,

Download the revised PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur    I modified your source data to show you what I mean about the 0% sometimes repeating twice.    The created date of the first 0% could be just one second before the last created date.   I am not completely sure why this happens in our data set, but it does happen.    I modified the record below to show this example.   How to account for this situation?    Currently,  it shows 2 opportunities at 80% for closed lost  (it is counting the opportunity twice).     Things work great otherwise, but we do have the situation with the 0% happening on some of the opportunities (not sure why but I don't think it is an error with the data...just probably means there was one other event logged in the system immediately prior to the last entry being created).   


texmexdragon2_0-1664166154766.png

 

Hi,

At 80% probability, the 2 that you see there are of 0065e00000ClhQsAAJ and 0065e00000Clk3EAAR.  Drag OpportunityID to the row labels and you will see what i mean.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur    Yes, but if you replicate my real data set, which has this scenario, then you will get 2 for the closed lost for the opportunity ending in 4iaaf.   I mentioned above that I altered your data source in your example to add an additional 0% row.    (which is how some of the rows in my actual full data set are).   

 

texmexdragon2_0-1664193024213.png

 

If you do that then you get this: 

texmexdragon2_1-1664193166664.png

 

 

Share your new input data in a format that i can paste it in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur    Hi Ashish  -  Note below in red.   (I did make the created date one second earlier for the first 0%).   

 

OpportunityIdCreatedDateProbabilityForecastCategory

0065e00000ClR4iAAF01/10/2021 22:1680%Pipeline
0065e00000ClR4iAAF02/10/2021 23:0180%Pipeline
0065e00000ClR4iAAF05/10/2021 17:4480%Pipeline
0065e00000ClR4iAAF13/12/2021 23:0580%Pipeline
0065e00000ClR4iAAF13/12/2021 23:1680%Pipeline
0065e00000ClR4iAAF13/01/2022 20:330%Omitted
0065e00000ClR4iAAF13/01/2022 20:340%Omitted
0065e00000ClQhEAAV01/10/2021 22:16100%Closed
0065e00000ClQhEAAV02/10/2021 22:59100%Closed
0065e00000ClkHVAAZ05/10/2021 14:5030%Pipeline
0065e00000ClkHVAAZ05/10/2021 14:5030%Pipeline
0065e00000ClkHVAAZ21/12/2021 18:4830%Pipeline
0065e00000ClkHVAAZ14/03/2022 00:520%Omitted
0065e00000Clk3EAAR05/10/2021 12:4540%Pipeline
0065e00000Clk3EAAR05/10/2021 12:4540%Pipeline
0065e00000Clk3EAAR05/10/2021 12:5340%Pipeline
0065e00000Clk3EAAR05/10/2021 13:0950%Pipeline
0065e00000Clk3EAAR15/10/2021 15:5850%Pipeline
0065e00000Clk3EAAR15/10/2021 16:0150%Pipeline
0065e00000Clk3EAAR18/10/2021 18:0450%Pipeline
0065e00000Clk3EAAR21/10/2021 18:3350%Pipeline
0065e00000Clk3EAAR27/10/2021 21:1280%Pipeline
0065e00000Clk3EAAR27/10/2021 21:2280%Pipeline
0065e00000Clk3EAAR29/10/2021 14:2680%Pipeline
0065e00000Clk3EAAR11/11/2021 19:18100%Closed
0065e00000CliqTAAR05/10/2021 00:2330%Pipeline
0065e00000CliqTAAR05/10/2021 00:2330%Pipeline
0065e00000CliqTAAR21/12/2021 17:2130%Pipeline
0065e00000CliqTAAR14/01/2022 02:2830%Pipeline
0065e00000CliqTAAR14/01/2022 02:2920%Pipeline
0065e00000CliqTAAR14/01/2022 02:3840%Pipeline
0065e00000CliqTAAR14/01/2022 02:3830%Pipeline
0065e00000CliqTAAR03/02/2022 18:1130%Pipeline
0065e00000CliqTAAR05/08/2022 14:5730%Pipeline
0065e00000CliqTAAR22/08/2022 08:0030%Pipeline
0065e00000ClhQsAAJ04/10/2021 17:3530%Pipeline
0065e00000ClhQsAAJ04/10/2021 17:3530%Pipeline
0065e00000ClhQsAAJ06/10/2021 14:0730%Pipeline
0065e00000ClhQsAAJ26/10/2021 13:0330%Pipeline
0065e00000ClhQsAAJ26/10/2021 14:1140%Pipeline
0065e00000ClhQsAAJ26/10/2021 14:1150%Pipeline
0065e00000ClhQsAAJ26/10/2021 14:1260%Pipeline
0065e00000ClhQsAAJ26/10/2021 14:1270%Pipeline
0065e00000ClhQsAAJ30/11/2021 17:0370%Pipeline
0065e00000ClhQsAAJ30/11/2021 17:0670%Pipeline
0065e00000ClhQsAAJ30/11/2021 17:0670%Pipeline
0065e00000ClhQsAAJ06/12/2021 15:0570%Pipeline
0065e00000ClhQsAAJ06/12/2021 15:0570%Pipeline
0065e00000ClhQsAAJ10/01/2022 20:2470%Pipeline
0065e00000ClhQsAAJ10/01/2022 20:2470%Pipeline
0065e00000ClhQsAAJ22/01/2022 18:2370%Pipeline
0065e00000ClhQsAAJ24/01/2022 17:0170%Pipeline
0065e00000ClhQsAAJ14/02/2022 19:3370%Pipeline
0065e00000ClhQsAAJ14/02/2022 19:3970%Pipeline
0065e00000ClhQsAAJ14/02/2022 19:4670%Pipeline
0065e00000ClhQsAAJ14/02/2022 19:5270%Pipeline
0065e00000ClhQsAAJ14/02/2022 19:5580%Pipeline
0065e00000ClhQsAAJ22/02/2022 20:4380%Pipeline
0065e00000ClhQsAAJ22/02/2022 21:05100%Closed

Hi,

Download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur   That worked....but if I may ask one more thing.   There is a scenario right below the red zeros....you will see two rows with 100%.    I believe in these scenarios in Salesforce the opportunity must have been unexpected and the salesperson just put it in as immediately closed-won  (100%).    So the 1st row is some sort of system entry they did, and they came back a few minutes later to change to make another edit.   In this case, the penultimate row is already at 100%.   

@Ashish_Mathur   Here are the rows I am talking about:  

texmexdragon2_0-1664196072900.png

 

Neither have you told me what the exact result should be nor have you shared data in a format that can be pasted in an MS Excel file.  How do you expect me to help you? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur    Hi Ashish  -  You're help so far has been outstanding!  And the matrix table below is exactly what I was looking for.    This is with the full data set.   Notice that it says there are 2,530 opportunities with no penultimate date - this is because the opportunity was set at 100% for each record of that opportunity.   (see example below).    Likekly these were opportunities that just never went through all of the typical CRM stages so they just got put in as closed-won (100%), but I would still like to show their frequency.    I tried working with your measures and columns today but could not achieve it.    Does this help?  

texmexdragon2_0-1664250861687.png

 

You could past this table into your source data: 

 

OpportunityIdCreatedDateForecastCategoryStageNameProbability
0065e00000Cs6fdAAB11/5/2021 11:33ClosedPO Received100%
0065e00000Cs6fdAAB11/5/2021 11:37ClosedPO Received100%
0065e00000Cs6fdAAB11/10/2021 15:50ClosedPO Received100%
0065e00000Cs6fdAAB11/10/2021 15:50ClosedPO Received100%
0065e00000Cs6fcAAB11/5/2021 11:29ClosedPO Received100%
0065e00000Cs6fcAAB11/5/2021 11:29ClosedPO Received100%
0065e00000Cs6fcAAB11/5/2021 11:30ClosedPO Received100%
0065e00000Cs6fcAAB11/10/2021 15:51ClosedPO Received100%
0065e00000CrRkMAAV10/29/2021 16:46ClosedPO Received100%
0065e00000CrRkMAAV10/29/2021 16:46ClosedPO Received100%
0065e00000CrRkMAAV10/29/2021 16:48ClosedPO Received100%
0065e00000CrRkMAAV11/7/2021 10:47ClosedPO Received100%
0065e00000CrRkMAAV11/10/2021 16:21ClosedPO Received100%
0065e00000CrRkMAAV12/6/2021 15:04ClosedPO Received100%

Hi,

Download the revised PBI file from here.  I have pasted the additional rows below the rows which i had in the table of the V2 file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur   Hi Ashish  -  It is working great for the 100% (see below).     Where would I adjust the code to also make sure the same situation for the 0% is taken care of?    If you see the last row below for Omitted (closed-lost, 0%) is having the same problem.   I tried replicating part of your code (the IF statements) and just changed the 1 to 0, but that did not seem to work.  

 

texmexdragon2_0-1664278779291.png

 

texmexdragon2_1-1664279675986.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.