Funnel Sorting

[fernandoC]

Hi Team,

I've built a formula based on 2 different columns ("Status Name Complete) & ("Status Order Complete"). These formulas has been used before on other proyects and it works perfectly, however, for some reason it's not ordering properly each stage of the funnel. 
Formula used: 
Funnel =

VAR applications = COUNTROWS('Main Jira Info')

VAR Funnel = CALCULATE (

applications,

FILTER (

ALL ( 'Main Jira Info'[Status Name Complete], 'Main Jira Info'[Status Order Complete] ),

'Main Jira Info'[Status Order Complete] >= MAX ( 'Main Jira Info'[Status Order Complete] )

)

)

VAR TotalStatus = COUNT('Main Jira Info'[Status Order Complete])

RETURN CALCULATE(IF(ISBLANK(TotalStatus),BLANK(),Funnel))

Image 1: Columns being used for this formula

Image 2: Current funnel, based on the results it should have the following distribution
- 14 Screening
- 10 HR Interview
- 9 Technical Interview
- 8 Final Interview 
- 4 Hired

Any ideas would be greatly appreciated. 

Thanks!

[AiolosZhao]

Hi @fernandoC ,

I tried it on my desktop, it works. Please see the below screenshot.

If it's not worked on your side, could you please show your data model and it's better to give a pbix to us.

Measure 9 = CALCULATE(COUNTROWS('Table (3)'),FILTER(ALL('Table (3)'[Order],'Table (3)'[Name]),'Table (3)'[Order] >= MAX('Table (3)'[Order])))

Thanks.

Aiolos 

[fernandoC]

Hi @AiolosZhao ,

I've uploaded the pbix file to Drive. Please use the following link: https://drive.google.com/file/d/1KalG1Sf_OrWdG5EB1Szvcg9qwutq-fhR/view?usp=sharing

Thanks again!. 

Best,

[AiolosZhao · Accepted solution]

Hi @fernandoC ,

Please see the below screenshot, I think that's what you want.

Funnel = CALCULATE (
COUNTROWS('Main Jira Info'),
FILTER (
ALL ( 'Main Jira Info'[Status Order Complete], 'Main Jira Info'[Status Name Complete] ),
'Main Jira Info'[Status Order Complete] <= MAX('Main Jira Info'[Status Order Complete]) && NOT(ISBLANK('Main Jira Info'[Status Name Complete]))
)
)

Please try.

Aiolos Zhao

[fernandoC]

Hi @AiolosZhao ,

Thank you for your help on this!. 

The formula worked 🙂 I only needed to make a quick fix to it as with the current formula it wasn't organizing the steps based on the order number. 

The final formula looked like this:

Funnel = CALCULATE (

COUNTROWS('Main Jira Info'),

FILTER (

ALL ( 'Main Jira Info'[Status Order Complete], 'Main Jira Info'[Status Name Complete] ),

'Main Jira Info'[Status Order Complete] >= MAX('Main Jira Info'[Status Order Complete]) && NOT(ISBLANK('Main Jira Info'[Status Name Complete]))

)

)

Thanks again for your tremendous help! 

Best,

FC.