Forum Discussion

dkernen's avatar
dkernen
Icon for Resolver II rankResolver II
5 years ago
Solved

MAX Value within Group - DAX

Hello.  I need some assistance.  I need to find the max registrants per county per year.   CountyState Population18+ Registrants RptQtr Annual Max Registrants Adair, MO 20,809 13,267 2...
Need Help
  • dkernen's avatar
    dkernen
    5 years ago

    I found the answer on a SQLBI video which does not require the calculated column.  Yea!

    Top State Rate =
    CALCULATE(
    MAXX(
    factRegistry,
    DIVIDE(
    factRegistry[Registrants],
    factRegistry[Population18+]
    )
    ),
    VALUES(Counties[StateName]),
    ALL(Counties)
    )
  • dkernen's avatar
    dkernen
    5 years ago

    Hi everyone.  This is the solution that did the trick.  I appreciate the help along the way!

    State Rate Max =

    CALCULATE(
    MAXX(
    factRegistry,
    DIVIDE(
    factRegistry[Registrants],
    factRegistry[Population18+]
    )
    ),
    VALUES(Counties[StateName]),
    ALL(Counties)
    )
FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
5 years ago

Hi dkernen ,

I think you can do it like this:

 

 

Max Registrants = CALCULATE ( 
    [Registered Donors], 
    ALLEXCEPT ( Counties, Counties[CountyState] ) 
)

Average Registrants = CALCULATE (
    AVERAGE ( factRegistry[Registrants] ),
    ALLEXCEPT ( Counties, Counties[CountyState] )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

dkernen's avatar
dkernen
Icon for Resolver II rankResolver II
5 years ago

Hi everyone.  This is the solution that did the trick.  I appreciate the help along the way!

State Rate Max =

CALCULATE(
MAXX(
factRegistry,
DIVIDE(
factRegistry[Registrants],
factRegistry[Population18+]
)
),
VALUES(Counties[StateName]),
ALL(Counties)
)