To get year corresponding to maximum sales

[Anonymous]

I want to get the year corresponding to maximum sales. See data below

Input: 

Expected Output:

Help me with a DAX to get the result

[Anonymous]

Hi @Anonymous ,

I suggest you to create a rank measure, then add this measure into visual level filter and set it to show items when value =1.

Rank =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) ),
    CALCULATE ( SUM ( 'Table'[Sales] ) ),
    ,
    DESC,
    DENSE
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[goncalogeraldes · Accepted solution]

Hello there @Anonymous ! Check if this works for you:

Max Sales Year =
CALCULATE (
    SELECTEDVALUE ( 'Your_Table'[Year] ),
    FILTER ( 'Your_Table', [Sales] = MAX ( [Sales] ) )
)

For max sales you can use:

Max Sales =
 MAX ( 'Your_Table'[Sales] ) 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes