Stage Conversion Rate Funnel

[Ira_Ira]

Hi, I want to adjust a stage conversion calculation for a funnel in PowerBI desktop.

The complexity in properly calculating it is that many opportunities skip stages or even jump back stages which makes it hard to analyze the data in a funnel way and see at which stage opps normally drop.

This is a measure I used in order to account for the skipping of the stages:

Nr. of opps per stage = 
VAR CurrentStageId = MAX('Opportunity History'[Order])
RETURN
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Opportunity History',
            'Opportunity History'[OpportunityId],
            "Max Stage", MAX('Opportunity History'[Order])
        ),
        IF([Max Stage] >= CurrentStageId, 1)
    ),
    ALLSELECTED()
)

This is how my data looks like more or less:

Opportunity Id	Order	Stage Name	Territory	Lead Source	…	…
1234	1	A	US	MKT
1234	2	B	US	MKT
1234	3	C	US	MKT
1234	4	D	US	MKT
1234	5	E	US	MKT
1234	6	F	US	MKT
1234	7	G	US	MKT
1234	8	H	US	MKT
1234	9	I	US	MKT

The measure works almost always as expected (image 1), however when the scope I filter on contains only opps that didn't go through the whole sales cycle but rather got lost, I get a count for all the opps for the stages that followed the lost stage (image 2 example - out of 19 opps in 1st stage, 9 went to 2nd stage, 5 to 3rd stage and only 1 to 4th stage and as of stage 5 all opps were lost but based on the logic in my DAX to count skipped stages there's 19 counts for all those stages when they should in fact not appear in the funnel at all) In my visual I filter out the stage "Lost" from my chart.

Do you know how can I adjust my DAX to filter out these stages when the opps are lost?

[G23]

@Ira_Ira Hello! Did you ever find a solution for this? I have a very similar business need to address related to skipped stages in a funnel and I am also having difficulty working this one out. Thanks!

[Agucho76]

Hello @Ira_Ira 
I have similar issues with my funnels. 
Did you find a solution? How do you manage your data and measures?

Thanks!

[TomMartens]

Hey @Ira_Ira ,

Please take the time and create a pbix file that contains the sample data for the 19 opportunities but still reflects your data model. Upload the pbix to onedrive, google drive, or Dropbox and share the link. If you are using Excel to create the sample data, share the Excel file as well. Make sure that pbix contains a "working" example measure in question.

Regards,

Tom