MAX Value within Group - DAX

[dkernen]

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	20Q1	13,634
Adair, MO	20,809	13,352	20Q2	13,634
Adair, MO	20,809	13,511	20Q3	13,634
Adair, MO	20,809	13,634	20Q4	13,634
Adair, MO	20,900	13,740	21Q1	13,740

The population in my fact table changes annually.  The number of registrants changes quarterly.  In the end, I need to be able to create the MAX rate and the AVERAGE rate (reg/pop) based on my geography and year selection.  If I do not make a year selection, I need it to take the highest overall rate.  If I do not make a geography selection, I need it to be the highest overall rate.  I will then need to have separate cards for the highest overall rate by state.

I think I have figured out how to get the population by year, but I am struggling with selecting the highest number of registrants.  I am struggling to determine what I need (ALL, ALLEXCEPT, ALLSELECTED, etc).  Thank you so much.

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/Ef-MvOJxd0BNjd9qpbADJ_wBYMq932Xnu-i8V...

@dax @Max

[dkernen · Accepted solution]

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 · Accepted solution]

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)

)

[v-robertq-msft]

Hi, @dkernen 

According to your description and sample picture, I think you can use some calculated columns and a single calculate table to be placed into a table chart and some slicers to achieve your requirement, you can take a look at my steps and check if it can be useful:

1. Create two calculated columns in the table ‘factRegistry’:

DDS = DIVIDE('factRegistry'[Registrants],'factRegistry'[Population18+])

Country = RIGHT('factRegistry'[CountyState],2)

Year = VALUE("20"&LEFT('factRegistry'[RptQtr],2))

2. Create a calculated table like this:

Matrix table =

SUMMARIZE('factRegistry','factRegistry'[Country],'factRegistry'[Year],"MAX REG",MAX('factRegistry'[Registrants]),"MAX DDS",MAX('factRegistry'[DDS]))

3. Create a table and two slicers and place them like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[FrankAT]

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 · Accepted solution]

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)

)

[dkernen]

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-NW...