Forum Discussion
Lookup value based on multiple criteria from another table
Hi, how do I look up and sum up value based on on multiple criterias from another table?
Here, the criterias are Date and Name.
My first table is
| Date | Name | Total |
| Dec | A | 100 |
| Dec | A | 200 |
| Dec | B | 100 |
| June | A | 0 |
| June | C | 0 |
My second table
| Date | Name | Amount |
| Dec | A | 5 |
| Dec | A | 10 |
| Dec | B | 1 |
| June | A | 5 |
| June | C | 23 |
| June | C | 5 |
Required output:
| Date | Name | Total | Amount |
| Dec | A | 300 | 15 |
| Dec | B | 100 | 1 |
| June | A | 0 | 5 |
| June | C | 0 | 28 |
Thank you.
Hi Anonymous
Add one column as a Date-Name to both the First and the Second Table with this code:
Date-Name = 'Second Table'[Date]&"-"&'Second Table'[Name]Then try this code to add a new table:
Table = ADDCOLUMNS ( SUMMARIZE ( 'First Table', 'First Table'[Date], 'First Table'[Name], "Total", SUM ( 'First Table'[Total] ) ), "Amount", CALCULATE ( SUM ( 'Second Table'[Amount] ), ALLEXCEPT ( 'Second Table', 'Second Table'[Date-Name] ) ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
2 Replies
- VahidDM
Super User
Hi Anonymous
Add one column as a Date-Name to both the First and the Second Table with this code:
Date-Name = 'Second Table'[Date]&"-"&'Second Table'[Name]Then try this code to add a new table:
Table = ADDCOLUMNS ( SUMMARIZE ( 'First Table', 'First Table'[Date], 'First Table'[Name], "Total", SUM ( 'First Table'[Total] ) ), "Amount", CALCULATE ( SUM ( 'Second Table'[Amount] ), ALLEXCEPT ( 'Second Table', 'Second Table'[Date-Name] ) ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!