Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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
FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous ,

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)

Anonymous's avatar
Anonymous
Not applicable
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