Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Measure for Max Value Based on Year

Hi, I have a data set that has values and the year that value was recorded. I want to create a measure that will display the maximum value for that year when I add it to a table. For example, my dat...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous ,

    try like

    Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous ,

try like

Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you, that works.

    What if I want the average for each year?

    YearValueAverage
    20081

    1.66

    200831.66
    200811.66
    200944
    201033
    201053
    201013
    201132.5
    201122.5
    201211

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous,

      You can try below measure formula if it works:

      Measure =
CALCULATE (
    AVERAGE ( table[Value] ),
    ALLSELECTED ( table ),
    VALUES ( table[Year] )
)

      You can change the aggregate functions to apply different summary mode on your fields. (min, max, sum, average...)

      Regards,

      Xiaoxin Sheng