Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Cumulative funnel chart not working

Hi all,

I need to create a cumulative funnel of candidates throughout the status new applicant, candidates screen etc... (e.g. candidates in new applicant must also count candidates in later steps such as candidate screen, Hr interviews and everything that is in front of them).

 

 

 
The data model below is the one below and is working for all other measures and forumals I have built. I need to count the number of applicant names in table 1. In table 4 I have the order of the stages

 

 

 

The formula I applied is this one, and I tried also to use coutrows instead of counta but the results is the same

Funnel =
CALCULATE (
    COUNTA(LevOpportunityApp_applications[Applicant name]),
    FILTER (
    ALL ( 'LeverStages_order/type'[Stage order]),
    'LeverStages_order/type'[Stage order] <= MAX( 'LeverStages_order/type'[Stage order] )
)
)
 

The issue is almost the same as this topic https://community.powerbi.com/t5/Desktop/Cummulative-Measure-for-Funnel/m-p/203025

 

 

Can you help me?

 

Thanks

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I managed to fix it partially 

    1. The all was poiting to a column instead of a table only

    2. The sign of the maximum was inverted

     

    Funnel2 =

    CALCULATE (
        COUNTROWS(LevOpportunityApp_applications),
        FILTER (
                ALL ( 'LeverStages_order/type'), 'LeverStages_order/type'[Stage order] >= MAX( 'LeverStages_order/type'[Stage order] )
    ))
     
     
    Now the result is as follows which is ALMOST what I want. Now I have 2 issues;
    1. The issue with very long values e.g. the Status= Business review is due to the fact that such status didn't have any Candidates at all. Do you have any suggestions on how to not show these status if their value is zero? I can't hide such status as depending on the Job position, these statuses MIGHT be populated.
    2. The values inside the graphs are OK (%)
     
    Any idea how to fix these?