Lookupvalue based on Max criteria

[Anonymous]

Dear all

How can i lookup value in Table1 "Amount" column for with specific "Name" and Max "Order" and display in table 2 for each name

Table1

Name	Order	Amount
A	1	100
A	2	50
B	1	25
B	2	100
B	3	200
C	1	1000
C	2	500

Table2

Name	Amount
A	50
B	200
C	500

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I hope I understood your requirement correctly, and please correct me if I am wrong.

please check the measures, and I come up with the new table like below picture.

Amount Value by Name and MaxOrder =

VAR maxorder =

MAXX ( Table1, Table1[Order] )

VAR amountbymaxorder =

CALCULATE ( SUM ( Table1[Amount] ), Table1[Order] = maxorder )

RETURN

amountbymaxorder

Amount Value by Name and MaxOrder V2 (total is fixed) =

VAR newtable =

SUMMARIZE (

Names,

Names[Name],

"@amountbymaxorder", [Amount Value by Name and MaxOrder]

)

RETURN

SUMX ( newtable, [@amountbymaxorder] )

Please also check the link down below, which is the sample PBIX file.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

https://www.dropbox.com/s/syg69gju2dux6zl/moscholes%202021%200322.pbix?dl=0