Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 ) )
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 ) )
Works like a charm! Thank you.
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% |
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |