Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.