Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tonymaclaren
Helper I
Helper I

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.

 

CategoryPercentageYear
Category A24%2012
Category A45%2013
Category A32%2014
Category A56%2015
Category A9%2016
Category B45%2012
Category B46%2013
Category B89%2014
Category B32%2015
Category B70%2016

 

Thanks in advance.

Tony

1 ACCEPTED SOLUTION

@tonymaclaren

 

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
        )
    )

 

maxofcat.png

 


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
v-yulgu-msft
Employee
Employee

Hi @tonymaclaren,

 

Max Percentage =
CALCULATE (
    MAX ( 'User Table'[Percentage] ),
    FILTER (
        ALLEXCEPT ( 'User Table', 'User Table'[Category] ),
        'User Table'[Year] = MAX ( 'User Table'[Year] )
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Yuliana,

You have provided a column formula. Is it possible to use a measure instead ?

Tony

@tonymaclaren

 

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
        )
    )

 

maxofcat.png

 


Regards
Zubair

Please try my custom visuals

Works like a charm! Thank you.

 

drewlewis15
Solution Specialist
Solution Specialist

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] ) )
)

CategoryPercentageYear Max Percentage
Category A24%201224%
Category A45%201345%
Category A32%201432%
Category A56%201556%
Category A9%20169%
Category B45%201245%
Category B46%201346%
Category B89%201489%
Category B32%201532%
Category B70%201670%

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.