Forum Discussion

fernandoC's avatar
fernandoC
Helper V
5 years ago
Solved

Funnel Formula

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,
Need Help
  • fernandoC's avatar
    fernandoC
    5 years ago

    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!.

3 Replies

    • fernandoC's avatar
      fernandoC
      Helper V
      5 years ago

      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!.
  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    5 years ago

    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.