Forum Discussion
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...
Anonymous ,
try like
Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))
amitchandak
Super User
Super UserAnonymous ,
try like
Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))
Thank you, that works.
What if I want the average for each year?
| Year | Value | Average |
| 2008 | 1 | 1.66 |
| 2008 | 3 | 1.66 |
| 2008 | 1 | 1.66 |
| 2009 | 4 | 4 |
| 2010 | 3 | 3 |
| 2010 | 5 | 3 |
| 2010 | 1 | 3 |
| 2011 | 3 | 2.5 |
| 2011 | 2 | 2.5 |
| 2012 | 1 | 1 |
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