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

Hello FrankAT !

Thank you for your help.  It doesn't do exactly what I need - but we're heading in the right direction!  I need to be able to find the max registrants per county per year (for all the counties I have selected), then calculate the DDS rate (reg/pop) and then show that max rate.  Then I need to be able to find the highest (or average) max rate for an entire state.  I believe I need to create a temp table with CountyState, max registrants, and the rate - and then take the highest (or average) rate from that temp table.

 

 

I added a calculated table in dire hopes of this working, but still no go.  I'd MUCH prefer to use a measure than a calculated table with a bi-directional filter and a whole lot of extra measures - but I just don't know how.  
https://mwtn-my.sharepoint.com/:f:/g/personal/dkernen_mwtn_org/EgC2ceLcv31Ama6PbXyvPgIB3RfgND2hSV-NWYUMfKqIDQ?e=DflhR8