Lookup MAX value(s) with a measure / calculated column

[Mardob]

Hi all,

I got stucked with a following issue. I'm having a table with products, costs, prices and margins. I would like to vlookup product code with the highest margin value.

When there is just one highest margin value in a table, there is no issue. The problem appears when there are a few products with the same highest margin value.

The sample table looks as below:

CODE	COST	PRICE	MARGIN
12345	2	4	0.5
12346	7	14	0.5
12347	6	12	0.5
12348	12	19	0.37
12349	10	7	-0.43

The formula:

CODE_HIGHEST_VALUE = 

LOOKUPVALUE(SampleTable[CODE],SampleTable[MARGIN],MAX(SampleTable[MARGIN]))

And it doesn't work, as we have a few products with highest, 50% margin.

How can I calculate it (preferably with a measure but I believe it can be not possible) to show all affected product codes?

Thanks!

[Mardob · Accepted solution]

Thanks, guys, but your formulas are not working as I wished. But I have found the solution:

MaxCodes =

VAR MaxMargin =

    MAXX(SampleTable,SampleTable[MARGIN]

    )

RETURN

CONCATENATEX(

    FILTER(

        ALLSELECTED(SampleTable),

        SampleTable[MARGIN] = MaxMargin

    ),

    SampleTable[CODE],

    ","

)

[ThxAlot]

(Almost) Nothing can't be achieved by a proper DAX measure,

[amitchandak]

@Mardob , You can have measures

MARGIN p = Sum(Table[MARGIN])

TOP 1= calculate(Max(Table[CODE]), keepfilters( index(1, allselected(Table[CODE]), orderBy([MARGIN p], desc))) )

Plot this with Table code in visual

Or a measure like

CONCATANATEX(index(1, allselected(Table[CODE]), orderBy([MARGIN p], desc)), Table[CODE], ", ")

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U