Forum Discussion
highest value by category
Hi
I am strugling with a problem as follows:
I need a DAX measure to find the percentage that matches the highest year value for a given category. So Category A would equal 9 % and Category B would be 70%. Year is not a date value, it is a whole number field.
| Category | Percentage | Year |
| Category A | 24% | 2012 |
| Category A | 45% | 2013 |
| Category A | 32% | 2014 |
| Category A | 56% | 2015 |
| Category A | 9% | 2016 |
| Category B | 45% | 2012 |
| Category B | 46% | 2013 |
| Category B | 89% | 2014 |
| Category B | 32% | 2015 |
| Category B | 70% | 2016 |
Thanks in advance.
Tony
As a MEASURE.....
Max Percentage measure = VAR MaxYear = CALCULATE ( MAX ( 'User Table'[Year] ), ALLEXCEPT ( 'User Table', 'User Table'[Category] ) ) RETURN CALCULATE ( MAX ( 'User Table'[Percentage] ), FILTER ( ALLEXCEPT ( 'User Table', 'User Table'[Category] ), 'User Table'[Year] = MaxYear ) )
6 Replies
Hi tonymaclaren,
Max Percentage = CALCULATE ( MAX ( 'User Table'[Percentage] ), FILTER ( ALLEXCEPT ( 'User Table', 'User Table'[Category] ), 'User Table'[Year] = MAX ( 'User Table'[Year] ) ) )Best regards,
Yuliana Gu
Thank you Yuliana,
You have provided a column formula. Is it possible to use a measure instead ?
Tony
As a MEASURE.....
Max Percentage measure = VAR MaxYear = CALCULATE ( MAX ( 'User Table'[Year] ), ALLEXCEPT ( 'User Table', 'User Table'[Category] ) ) RETURN CALCULATE ( MAX ( 'User Table'[Percentage] ), FILTER ( ALLEXCEPT ( 'User Table', 'User Table'[Category] ), 'User Table'[Year] = MaxYear ) )
Max Percentage = CALCULATE(MAX('Table'[Percentage]), FILTER('Table', 'Table'[Year] = MAX('Table'[Year])))
Unfortunately that does not work, it merely duplicates the percentage column.
Max Percentage test =
CALCULATE (
MAX ( 'Table'[Percentage] ),
FILTER ( 'Table', 'Table'[Year] = MAX ( 'Table'[Year] ) )
)Category Percentage Year Max Percentage Category A 24% 2012 24% Category A 45% 2013 45% Category A 32% 2014 32% Category A 56% 2015 56% Category A 9% 2016 9% Category B 45% 2012 45% Category B 46% 2013 46% Category B 89% 2014 89% Category B 32% 2015 32% Category B 70% 2016 70%
