Funnel Formula

[fernandoC]

Hi!

I've built a formula to show a funnel including all candidates from each stage so that if they move from one stage to the other it replicates it's value instead of having 6-7 candidates with each stage by using a stage numeric order. The formula I'm using is:

- Funnel =

CALCULATE (

[- Applications],

FILTER (

ALL ( 'Region 2020 - Candidates'[Status], 'Region 2020 - Candidates'[Status Order] ),

'Region 2020 - Candidates'[Status Order] >= MAX ( 'Region 2020 - Candidates'[Status Order] )

)

)

----------------------------

[Applications] = countrows [candidate table]

The problem I've been having is that if there's no candidate that has passed an specific stage (i.e hires) that specific stage goes to the top of the funnel with stage order 0 which I know is expected of the formula. I would like to make a change in the formula so that if theres a stage without any value it doesn't show up in the visualization at all. 

In the image below the stage "Hire" should not be present since there's no values with stage "Hire" at the moment. 

Thanks in advance.

Best,

[v-diye-msft]

Hi @fernandoC 

Kindly check below whether helps:

- Funnel =
SUMX (
FILTER (
ALL ( 'Region 2020 - Candidates'[Status]),
'Region 2020 - Candidates'[Status Order] >= MAX ( 'Region 2020 - Candidates'[Status Order] )
),[- Applications]
)

If not,  you might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[mwegener]

Hi @fernandoC ,

can you provide an example file?

[fernandoC · Accepted solution]

Hi @mwegener @v-she-msft ,

I found the solution to my problem.

Just created a new measure with

IF(ISBLANK('Region 2020 - Candidates'[Total Status]),BLANK(),[- Funnel])

Thanks again for your help!.