How to show count at each stage using Funnel chart if each record keeps only latest stage in db

[Anonymous]

Hi all,

Is there a way to display my data using Funnel chart even if each record does not keep all the stages it gone through? Each deal goes through different stages but keeps only the latest stage record in db. 

ID	STAGE
Deal 1	Meeting
Deal 2	Engaged
Deal 3	Engaged
Deal 4	Initial Interview
Deal 5	Bid
Deal 6	PO Sent
Deal 7	Won
Deal 8	Won
Deal 9	Lost
Deal 10	Meeting
Deal 11	Initial Interview
Deal 12	Waiting
Deal 13	Bid
Deal 14	Won
Deal 15	Engaged
Deal 16	Engaged
Deal 17	Engaged
Deal 18	PO Sent
Deal 19	Technical Interview
Deal 20	Engaged
Deal 21
Deal 22
Deal 23
Deal 24
Deal 25

I computed the Total using these formula:

Table = UNION(SUMMARIZECOLUMNS('Deal'[Stage]),SUMMARIZECOLUMNS('Deal',"Stage","Totals"))

I created an inactive relationship  between Deal[Stage] and Table[Stage] then created a measure

Value = IF(SELECTEDVALUE('Table'[Stage])="Totals",CALCULATE(DISTINCTCOUNT('Deal'[Id]),ALLSELECTED('Deal')), CALCULATE(DISTINCTCOUNT('Deal'[Id]),USERELATIONSHIP('Deal'[Stage],'Table'[Stage__c])))

Result:

Stage	Value	Sequence
	5	1
Engaged	6	2
Meeting	2	3
Initial Interview	2	4
Technical Interview	1	5
Bid	2	6
PO Sent	2	7
Won	3	8
Lost	1	9
Waiting	1	10
Totals	25	0

What I'm getting is this 

Im looking to have something like

Thanks in advance!

[V-pazhen-msft · Accepted solution]

@Anonymous 

I am not very clear about the logic of the result you are looking for. Firstly, I am not sure whether the sequence is manual inputted or it is a calculated product, and seems like you are expecting a calculative sum of the count on stages, but you have 3,1,1 for the bottom 3 sequence which makes a bit confused.

However, I have created a pbix with some speculation, take a look which might helps.

1. Create a new table

Table = SUMMARIZE(Deal,[STAGE],[Sequence],"Count Stages",COUNT(Deal[STAGE]))

2. Create a new column in the new table.

Cumulative Count = CALCULATE(SUM('Table'[Count Stages]),FILTER('Table',[Sequence]>=EARLIER([Sequence])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[bmeduser]

@V-pazhen-msft I had the same issue for ages and it worked, thanks a lot!

[Anonymous]

Hi @V-pazhen-msft appreciate your time working on this. It worked! Thank you so much