Dax to show the year with max value

[Iamnvt]

I have a table below:

Product	Y1	Y2	Y3
A	1	2	3
B	1	1	1
C	3	3	3

need the result to show the Y with max total of all product (which is Y3: total = 10)

Expected result:

Product	Y3
A	3
B	1
C	3
Total	10

Thank you

[v-yanjiang-msft · Accepted solution]

Hi @Iamnvt ,

According to your description, I have two methods.

Method1

1.Create a calculated column in the table.

MAX-Y = 
VAR _SUMY1=SUM('Table'[Y1])
VAR _SUMY2=SUM('Table'[Y2])
VAR _SUMY3=SUM('Table'[Y3])
VAR _MAXsum=MAXX({_SUMY1,_SUMY2,_SUMY3},[Value])
RETURN
SWITCH(TRUE(),
_SUMY1=_MAXsum,'Table'[Y1],
_SUMY2=_MAXsum,'Table'[Y2],
_SUMY3=_MAXsum,'Table'[Y3])

2.If you want to show the year name, you can then create another measure.

MAX Year = 
VAR _SUMY1=SUM('Table'[Y1])
VAR _SUMY2=SUM('Table'[Y2])
VAR _SUMY3=SUM('Table'[Y3])
VAR _MAXsum=MAXX({_SUMY1,_SUMY2,_SUMY3},[Value])
RETURN
SWITCH(TRUE(),
_SUMY1=_MAXsum,"Y1",
_SUMY2=_MAXsum,"Y2",
_SUMY3=_MAXsum,"Y3")

Method2

1.Select Y1,Y2,Y3 at the same time, and then click unpivot columns, then get three columns.

2.Cteate a new column to calculate the max total value.

Column =
SUMX (
    FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Year] = MAX ( 'Table (2)'[Year] ) ),
    'Table (2)'[Value]
)

3.Create a measure.

Check =
VAR _SUM =
    SUMX (
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Year] = MAX ( 'Table (2)'[Year] ) ),
        'Table (2)'[Value]
    )
RETURN
    IF ( _SUM = MAX ( 'Table (2)'[Column] ), 1, 0 )

4.Put the measure in the visual filter and select it is 1, get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[parry2k]

@Iamnvt what @amitchandak suggest in the first part, unpivot the table, it will get you two columns Attribute and Value, rename it as you see fit and then just you need a sum measure

Sum = SUM ( Table[Value] )

to visualize, add matrix visual:

- product on rows

- attribute on columns

- measure on value

and you can filter on any year, in this case you will filer where attribute value is = Y3 so that the matrix visual only shows y3 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@Iamnvt ,

Unpivot the table Year(Y) will come on rows
rename that as Year
then create this measure in dax

measure =
var _max = Maxx(allselected(Table), Table[Year])
return
Sumx(filter(Table, Table[Year] = _max), Table[Value])

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Iamnvt]

@amitchandak 
How about if the _max value is at Y2? I need to filter the year with TOTAL MAX value.

Thanks