Forum Discussion
Lookupvalue based on Max criteria
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 |
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 )RETURNamountbymaxorderAmount Value by Name and MaxOrder V2 (total is fixed) =VAR newtable =SUMMARIZE (Names,Names[Name],"@amountbymaxorder", [Amount Value by Name and MaxOrder])RETURNSUMX ( 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!!
1 Reply
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 )RETURNamountbymaxorderAmount Value by Name and MaxOrder V2 (total is fixed) =VAR newtable =SUMMARIZE (Names,Names[Name],"@amountbymaxorder", [Amount Value by Name and MaxOrder])RETURNSUMX ( 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!!