Forum Discussion
Lookup for multiple columns and multiple values
I have tables like this:
Cat
| Year | Cat | Type | Budget |
| 2019 | A | New | 275 |
| 2020 | B | New | 200 |
| 2021 | C | New | 250 |
| 2019 | A | Ret | 275 |
| 2020 | B | Ret | 200 |
| 2021 | C | Ret | 250 |
| 2019 | A | Used | 275 |
| 2020 | B | Used | 200 |
| 2021 | C | Used | 250 |
| … |
Budget
| Year | Cat | Budget |
| 2020 | A | 100 |
| 2020 | B | 200 |
| 2020 | C | 150 |
| 2021 | A | 200 |
| 2021 | B | 225 |
| 2021 | C | 250 |
| 2019 | A | 275 |
| 2019 | B | 300 |
| 2019 | C | 325 |
I need to get column Budget in first table. I read solutions for lookupvalue formula for multiple columns but it only works for single values, and when I make a merge directly in the transformation data process, it sums the budget in the report view. Please help.
Basically is a double lookupvalue by year and cat, that provides multiple values.
Anonymous , Create a new column in Cat table
sumx(filter(Budget, Budget[Year] =Cat[Year] && Budget[cat] =Cat[Cat]),Budget[Budget])
2 Replies
Anonymous , Create a new column in Cat table
sumx(filter(Budget, Budget[Year] =Cat[Year] && Budget[cat] =Cat[Cat]),Budget[Budget])
Anonymous
if you want to use lookupvalue,you can try this.
Column = LOOKUPVALUE(budget[Budget],budget[Cat],'cat'[Cat],budget[Year],cat[Year])
