cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fernandoC
Helper V
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
funnel 3.PNG
 
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

funnel2.PNG
 
Any ideas would be greatly appreciated. 
 
Thanks!
 
1 ACCEPTED SOLUTION

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

 

Funnel Sorting 2.PNG

 

Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
AiolosZhao
Memorable Member
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])))

 

Funnel Sorting.PNG

 

Thanks.

Aiolos 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @AiolosZhao ,

I've uploaded the pbix file to Drive. Please use the following link: https://drive.google.com/file/d/1KalG1Sf_OrWdG5EB1Szvcg9qwutq-fhR/view?usp=sharing

 

Thanks again!. 

 

Best,

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

 

Funnel Sorting 2.PNG

 

Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!