DAX Measure for Max Value Based on Year

[Anonymous]

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:

Year	Value
2008	1
2008	3
2008	1
2009	4
2010	3
2010	5
2010	1
2011	3
2011	2
2012	1

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

Year	Value	Max
2008	1	3
2008	3	3
2008	1	3
2009	4	4
2010	3	5
2010	5	5
2010	1	5
2011	3	3
2011	2	3
2012	1	1

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

[neha369]

You can also try 

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

[amitchandak · Accepted solution]

@Anonymous ,

try like

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

[Anonymous]

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

[v-shex-msft]

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