cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: highest value by category

@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

 

Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Highlighted
Solution Specialist
Solution Specialist

Re: highest value by category

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

Highlighted
Microsoft
Microsoft

Re: highest value by category

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.
Highlighted
Helper I
Helper I

Re: highest value by category

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%
Highlighted
Helper I
Helper I

Re: highest value by category

Thank you Yuliana,

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

Tony

Highlighted
Super User III
Super User III

Re: highest value by category

@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

 

Try my new Power BI game Cross the River

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors