Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |