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)
    )
v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
5 years ago

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))

 

  1. Create a calculated table like this:
Matrix table =

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

 

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