Slicer applied on lead stage column, calculate next stages

[Shashi_Kant82]

Hi All, 

I've a column with Lead statuses and their IDs, See below screenshot.

1. I need to add stage column as a filter and if i select any one stage then next stages count should appear, for e.g. if i select "engaged" in slicer then it should show me the count of Leads which have travelled from engaged to MQL and then Engaged to  Opportunity(Next Row) and so on.

2. Stages are not in order so i also have to sort them in correct order (Screenshot below for the order).

3. I should be able to calculate the coversion % like how many Leads converted from engaged to MQL, engaged to Opportunity and so on.

4. There is also repetetion of stages like for ID 1 we can have multiple MQL rows.

Kindly suggest. Thanks in advance. 

[v-stephen-msft · Accepted solution]

Hi @Shashi_Kant82 ,

Here's my solution.

1.Create a table for the slicer with stage and sort. I created it by entering data and there's no relationship between tables. 

2.Create a column to get the sort number in Table.

Sort Nmuber = CALCULATE(MAX('Table for slicer'[Sort Number]),FILTER('Table for slicer',[Stage]=EARLIER('Table'[Stage])))

3.Create a measure to return countings.

Count = var _num=SELECTEDVALUE('Table for slicer'[Sort Number])+2
return CALCULATE(COUNT('Table'[ID]),FILTER(ALLSELECTED('Table'),[Sort Nmuber]=_num))

If "Engaged" is selected, the measure returns 3.

4.If you want to calculate the percentage, and then calculate the total number of IDs, you can divide them, and so on.

Percentage = var _total=CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLSELECTED('Table'))
return DIVIDE([Count],_total)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Shashi_Kant82]

guys, Please help. i am stuck here.