Forum Discussion

fernandoC's avatar
fernandoC
Helper V
5 years ago
Solved

Funnel Sorting

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!
 
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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