cancel
Showing results for
Did you mean:
Helper V

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

1 ACCEPTED SOLUTION
Memorable Member

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]))
)
)``````

Aiolos Zhao

Proud to be a Super User!

4 REPLIES 4
Memorable Member

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

Proud to be a Super User!

Helper V

Hi @AiolosZhao ,

Thanks again!.

Best,

Memorable Member

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]))
)
)``````

Aiolos Zhao

Proud to be a Super User!

Helper V

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.

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!