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 data looks like this:

YearValue
20081
20083
20081
20094
20103
20105
20101
20113
20112
20121

 

So the measure I want would add a column to the table that looks like this:

YearValueMax
200813
200833
200813
200944
201035
201055
201015
201133
201123
201211

 

I tried:

Measure = CALCULATE(MAX('Table'[Value]))
but it just returns the number in the Value column.
I also need to do the same for Min, but I figure that will be the same DAX just with MIN instead of MAX.
 
Thanks
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous ,

    try like

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

4 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You can also try 

     

    Column = CALCULATE(MAX('Table'[Value]),GROUPBY('Table','Table'[Value]))