Forum Discussion

dkernen's avatar
dkernen
Resolver 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.

 
CountyStatePopulation18+RegistrantsRptQtrAnnual Max Registrants
Adair, MO20,80913,26720Q113,634
Adair, MO20,80913,35220Q213,634
Adair, MO20,80913,51120Q313,634
Adair, MO20,80913,63420Q413,634
Adair, MO20,90013,74021Q113,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-i8VeevF1FI3A?e=4ppwHt

 

dax Max

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

5 Replies

  • FrankAT's avatar
    FrankAT
    Community 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
      Resolver 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

    • dkernen's avatar
      dkernen
      Resolver 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)
      )
  • v-robertq-msft's avatar
    v-robertq-msft
    Community 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.

  • dkernen's avatar
    dkernen
    Resolver II
    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)
    )