Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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.
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.
@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.
OpportunityId | CreatedDate | Probability | ForecastCategory |
0065e00000ClR4iAAF | 10/1/2021 22:16 | 80% | Pipeline |
0065e00000ClR4iAAF | 10/2/2021 23:01 | 80% | Pipeline |
0065e00000ClR4iAAF | 10/5/2021 17:44 | 80% | Pipeline |
0065e00000ClR4iAAF | 12/13/2021 23:05 | 80% | Pipeline |
0065e00000ClR4iAAF | 12/13/2021 23:16 | 80% | Pipeline |
0065e00000ClR4iAAF | 1/13/2022 20:34 | 0% | Omitted |
0065e00000ClQhEAAV | 10/1/2021 22:16 | 100% | Closed |
0065e00000ClQhEAAV | 10/2/2021 22:59 | 100% | Closed |
0065e00000ClkHVAAZ | 10/5/2021 14:50 | 30% | Pipeline |
0065e00000ClkHVAAZ | 10/5/2021 14:50 | 30% | Pipeline |
0065e00000ClkHVAAZ | 12/21/2021 18:48 | 30% | Pipeline |
0065e00000ClkHVAAZ | 3/14/2022 0:52 | 0% | Omitted |
0065e00000Clk3EAAR | 10/5/2021 12:45 | 40% | Pipeline |
0065e00000Clk3EAAR | 10/5/2021 12:45 | 40% | Pipeline |
0065e00000Clk3EAAR | 10/5/2021 12:53 | 40% | Pipeline |
0065e00000Clk3EAAR | 10/5/2021 13:09 | 50% | Pipeline |
0065e00000Clk3EAAR | 10/15/2021 15:58 | 50% | Pipeline |
0065e00000Clk3EAAR | 10/15/2021 16:01 | 50% | Pipeline |
0065e00000Clk3EAAR | 10/18/2021 18:04 | 50% | Pipeline |
0065e00000Clk3EAAR | 10/21/2021 18:33 | 50% | Pipeline |
0065e00000Clk3EAAR | 10/27/2021 21:12 | 80% | Pipeline |
0065e00000Clk3EAAR | 10/27/2021 21:22 | 80% | Pipeline |
0065e00000Clk3EAAR | 10/29/2021 14:26 | 80% | Pipeline |
0065e00000Clk3EAAR | 11/11/2021 19:18 | 100% | Closed |
0065e00000CliqTAAR | 10/5/2021 0:23 | 30% | Pipeline |
0065e00000CliqTAAR | 10/5/2021 0:23 | 30% | Pipeline |
0065e00000CliqTAAR | 12/21/2021 17:21 | 30% | Pipeline |
0065e00000CliqTAAR | 1/14/2022 2:28 | 30% | Pipeline |
0065e00000CliqTAAR | 1/14/2022 2:29 | 20% | Pipeline |
0065e00000CliqTAAR | 1/14/2022 2:38 | 40% | Pipeline |
0065e00000CliqTAAR | 1/14/2022 2:38 | 30% | Pipeline |
0065e00000CliqTAAR | 2/3/2022 18:11 | 30% | Pipeline |
0065e00000CliqTAAR | 8/5/2022 14:57 | 30% | Pipeline |
0065e00000CliqTAAR | 8/22/2022 8:00 | 30% | Pipeline |
0065e00000ClhQsAAJ | 10/4/2021 17:35 | 30% | Pipeline |
0065e00000ClhQsAAJ | 10/4/2021 17:35 | 30% | Pipeline |
0065e00000ClhQsAAJ | 10/6/2021 14:07 | 30% | Pipeline |
0065e00000ClhQsAAJ | 10/26/2021 13:03 | 30% | Pipeline |
0065e00000ClhQsAAJ | 10/26/2021 14:11 | 40% | Pipeline |
0065e00000ClhQsAAJ | 10/26/2021 14:11 | 50% | Pipeline |
0065e00000ClhQsAAJ | 10/26/2021 14:12 | 60% | Pipeline |
0065e00000ClhQsAAJ | 10/26/2021 14:12 | 70% | Pipeline |
0065e00000ClhQsAAJ | 11/30/2021 17:03 | 70% | Pipeline |
0065e00000ClhQsAAJ | 11/30/2021 17:06 | 70% | Pipeline |
0065e00000ClhQsAAJ | 11/30/2021 17:06 | 70% | Pipeline |
0065e00000ClhQsAAJ | 12/6/2021 15:05 | 70% | Pipeline |
0065e00000ClhQsAAJ | 12/6/2021 15:05 | 70% | Pipeline |
0065e00000ClhQsAAJ | 1/10/2022 20:24 | 70% | Pipeline |
0065e00000ClhQsAAJ | 1/10/2022 20:24 | 70% | Pipeline |
0065e00000ClhQsAAJ | 1/22/2022 18:23 | 70% | Pipeline |
0065e00000ClhQsAAJ | 1/24/2022 17:01 | 70% | Pipeline |
0065e00000ClhQsAAJ | 2/14/2022 19:33 | 70% | Pipeline |
0065e00000ClhQsAAJ | 2/14/2022 19:39 | 70% | Pipeline |
0065e00000ClhQsAAJ | 2/14/2022 19:46 | 70% | Pipeline |
0065e00000ClhQsAAJ | 2/14/2022 19:52 | 70% | Pipeline |
0065e00000ClhQsAAJ | 2/14/2022 19:55 | 80% | Pipeline |
0065e00000ClhQsAAJ | 2/22/2022 20:43 | 80% | Pipeline |
0065e00000ClhQsAAJ | 2/22/2022 21:05 | 100% | 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.
@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.
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.
@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%?
Hi,
Download the revised PBI file from here.
Hope this helps.
@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).
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.
@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).
If you do that then you get this:
Share your new input data in a format that i can paste it in an MS Excel file.
@Ashish_Mathur Hi Ashish - Note below in red. (I did make the created date one second earlier for the first 0%).
OpportunityIdCreatedDateProbabilityForecastCategory
0065e00000ClR4iAAF | 01/10/2021 22:16 | 80% | Pipeline |
0065e00000ClR4iAAF | 02/10/2021 23:01 | 80% | Pipeline |
0065e00000ClR4iAAF | 05/10/2021 17:44 | 80% | Pipeline |
0065e00000ClR4iAAF | 13/12/2021 23:05 | 80% | Pipeline |
0065e00000ClR4iAAF | 13/12/2021 23:16 | 80% | Pipeline |
0065e00000ClR4iAAF | 13/01/2022 20:33 | 0% | Omitted |
0065e00000ClR4iAAF | 13/01/2022 20:34 | 0% | Omitted |
0065e00000ClQhEAAV | 01/10/2021 22:16 | 100% | Closed |
0065e00000ClQhEAAV | 02/10/2021 22:59 | 100% | Closed |
0065e00000ClkHVAAZ | 05/10/2021 14:50 | 30% | Pipeline |
0065e00000ClkHVAAZ | 05/10/2021 14:50 | 30% | Pipeline |
0065e00000ClkHVAAZ | 21/12/2021 18:48 | 30% | Pipeline |
0065e00000ClkHVAAZ | 14/03/2022 00:52 | 0% | Omitted |
0065e00000Clk3EAAR | 05/10/2021 12:45 | 40% | Pipeline |
0065e00000Clk3EAAR | 05/10/2021 12:45 | 40% | Pipeline |
0065e00000Clk3EAAR | 05/10/2021 12:53 | 40% | Pipeline |
0065e00000Clk3EAAR | 05/10/2021 13:09 | 50% | Pipeline |
0065e00000Clk3EAAR | 15/10/2021 15:58 | 50% | Pipeline |
0065e00000Clk3EAAR | 15/10/2021 16:01 | 50% | Pipeline |
0065e00000Clk3EAAR | 18/10/2021 18:04 | 50% | Pipeline |
0065e00000Clk3EAAR | 21/10/2021 18:33 | 50% | Pipeline |
0065e00000Clk3EAAR | 27/10/2021 21:12 | 80% | Pipeline |
0065e00000Clk3EAAR | 27/10/2021 21:22 | 80% | Pipeline |
0065e00000Clk3EAAR | 29/10/2021 14:26 | 80% | Pipeline |
0065e00000Clk3EAAR | 11/11/2021 19:18 | 100% | Closed |
0065e00000CliqTAAR | 05/10/2021 00:23 | 30% | Pipeline |
0065e00000CliqTAAR | 05/10/2021 00:23 | 30% | Pipeline |
0065e00000CliqTAAR | 21/12/2021 17:21 | 30% | Pipeline |
0065e00000CliqTAAR | 14/01/2022 02:28 | 30% | Pipeline |
0065e00000CliqTAAR | 14/01/2022 02:29 | 20% | Pipeline |
0065e00000CliqTAAR | 14/01/2022 02:38 | 40% | Pipeline |
0065e00000CliqTAAR | 14/01/2022 02:38 | 30% | Pipeline |
0065e00000CliqTAAR | 03/02/2022 18:11 | 30% | Pipeline |
0065e00000CliqTAAR | 05/08/2022 14:57 | 30% | Pipeline |
0065e00000CliqTAAR | 22/08/2022 08:00 | 30% | Pipeline |
0065e00000ClhQsAAJ | 04/10/2021 17:35 | 30% | Pipeline |
0065e00000ClhQsAAJ | 04/10/2021 17:35 | 30% | Pipeline |
0065e00000ClhQsAAJ | 06/10/2021 14:07 | 30% | Pipeline |
0065e00000ClhQsAAJ | 26/10/2021 13:03 | 30% | Pipeline |
0065e00000ClhQsAAJ | 26/10/2021 14:11 | 40% | Pipeline |
0065e00000ClhQsAAJ | 26/10/2021 14:11 | 50% | Pipeline |
0065e00000ClhQsAAJ | 26/10/2021 14:12 | 60% | Pipeline |
0065e00000ClhQsAAJ | 26/10/2021 14:12 | 70% | Pipeline |
0065e00000ClhQsAAJ | 30/11/2021 17:03 | 70% | Pipeline |
0065e00000ClhQsAAJ | 30/11/2021 17:06 | 70% | Pipeline |
0065e00000ClhQsAAJ | 30/11/2021 17:06 | 70% | Pipeline |
0065e00000ClhQsAAJ | 06/12/2021 15:05 | 70% | Pipeline |
0065e00000ClhQsAAJ | 06/12/2021 15:05 | 70% | Pipeline |
0065e00000ClhQsAAJ | 10/01/2022 20:24 | 70% | Pipeline |
0065e00000ClhQsAAJ | 10/01/2022 20:24 | 70% | Pipeline |
0065e00000ClhQsAAJ | 22/01/2022 18:23 | 70% | Pipeline |
0065e00000ClhQsAAJ | 24/01/2022 17:01 | 70% | Pipeline |
0065e00000ClhQsAAJ | 14/02/2022 19:33 | 70% | Pipeline |
0065e00000ClhQsAAJ | 14/02/2022 19:39 | 70% | Pipeline |
0065e00000ClhQsAAJ | 14/02/2022 19:46 | 70% | Pipeline |
0065e00000ClhQsAAJ | 14/02/2022 19:52 | 70% | Pipeline |
0065e00000ClhQsAAJ | 14/02/2022 19:55 | 80% | Pipeline |
0065e00000ClhQsAAJ | 22/02/2022 20:43 | 80% | Pipeline |
0065e00000ClhQsAAJ | 22/02/2022 21:05 | 100% | Closed |
Hi,
Download the PBI file from here.
Hope this helps.
@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%.
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?
@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?
You could past this table into your source data:
OpportunityId | CreatedDate | ForecastCategory | StageName | Probability |
0065e00000Cs6fdAAB | 11/5/2021 11:33 | Closed | PO Received | 100% |
0065e00000Cs6fdAAB | 11/5/2021 11:37 | Closed | PO Received | 100% |
0065e00000Cs6fdAAB | 11/10/2021 15:50 | Closed | PO Received | 100% |
0065e00000Cs6fdAAB | 11/10/2021 15:50 | Closed | PO Received | 100% |
0065e00000Cs6fcAAB | 11/5/2021 11:29 | Closed | PO Received | 100% |
0065e00000Cs6fcAAB | 11/5/2021 11:29 | Closed | PO Received | 100% |
0065e00000Cs6fcAAB | 11/5/2021 11:30 | Closed | PO Received | 100% |
0065e00000Cs6fcAAB | 11/10/2021 15:51 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 10/29/2021 16:46 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 10/29/2021 16:46 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 10/29/2021 16:48 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 11/7/2021 10:47 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 11/10/2021 16:21 | Closed | PO Received | 100% |
0065e00000CrRkMAAV | 12/6/2021 15:04 | Closed | PO Received | 100% |
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.
@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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
94 | |
89 | |
32 | |
28 |