Grouping by a field and finding the max

[aashton]

Hello,

So I have the following table structure:  

And use this DAX formula to find the total amount of Deals by Owner by Phase (one for each phase):

Phase 1 2022 Approvals Count by Owner =

CALCULATE(

DISTINCTCOUNT('Deal Review'[Deals Reviewed (Since 2019)]),

FILTER(

ALLEXCEPT('Deal Review','Deal Review'[Owner]),

SEARCH("2022", 'Deal Review'[Phase 1], , BLANK() ) > 0

))

I would now like to display on cards, the owners name that has the most Phase 1, Phase 2 and Phase 3's.  Not sure how to accomplish this?

Thank you.

[v-xiaotang · Accepted solution]

Hi @aashton 

Thanks for reaching out to us.

Let's take phase 1 as an example, create the measures

-

Phase 1 2022 Approvals Count by Owner =
CALCULATE (
    DISTINCTCOUNT ( 'Deal Review'[Deals Reviewed (Since 2019)] ),
    FILTER (
        ALL ( 'Deal Review' ),
        'Deal Review'[Owner] = MIN ( 'Deal Review'[Owner] )
            && SEARCH ( "2022", 'Deal Review'[Phase 1],, BLANK () ) <> BLANK ()
    )
)

-

max owner =
VAR _max =
    MAXX ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] )
RETURN
    CALCULATE (
        MAX ( 'Deal Review'[Owner] ),
        FILTER ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] = _max )
    )

result 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[aashton]

Yes, that worked!!! Thank you so much