Forum Discussion

jsw94's avatar
jsw94
Regular Visitor
1 year ago

How to filter within a measure?

Hello,

 

I'm very new to Power BI. I'm trying to create a single funnel to show a pipeline of partner companies for materials sourcing. I have created a custom measure that makes these stages add up cumulatively in the funnel.

 

Here is part of my table:

 

My measure: 

 

Count of Current Status running total in Current Status 2 =
CALCULATE(
    COUNTA('Potential partners'[Current Status]),
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Status', 'Status'[Order], 'Status'[Current Status]),
            ALLSELECTED('Status')
        ),
        ISONORAFTER(
            'Status'[Order], MIN('Status'[Order]), ASC,
            'Status'[Current Status], MIN('Status'[Current Status]), ASC
        )
    )
)

 

And my funnel:

 

 

I would like to include the "Disqualified" stages in my main funnel, but I do not want those numbers to be cumulative. How can I exclude them so that "Disqualified - Preliminary" stays 3, instead of becoming 6 when added to the main funnel with the measure included. In this case, "Inquired Made" would equal 28 and preliminary discussions would be 9 because those three that have been disqualified would be counted in the main funnel.