how to bring the field to from one table to another by matching year and product column

[PBI_newuser]

Hi,

How to lookup Sales field to "Hours" table by matching both "FY" and "Product"?

https://wetransfer.com/downloads/1d0562a2689734545d1256f4c9b9410f20201229060942/7103ecfb2d8bbbac6a78...

[sanalytics · Accepted solution]

Hello @PBI_newuser ,

Create a virtual relationship between two tables..

Use below dax

Sales = CALCULATE(

SUM(Sales[Sales]),

TREATAS(VALUES(Hours[FY]),Sales[FY]),

TREATAS(VALUES(Hours[Product Group]),Sales[Product]))

Attached is the Power Bi file

Hope it will help

Regards

Sanalytics

https://dropfiles.org/amWJyl0G

[amitchandak]

@PBI_newuser , Create a new column in hours table like

sumx(filter(sales, sales[product] = hours[product] && sales[FY] = hours[FY]), sales[sales])

refer : https://www.youtube.com/watch?v=czNHt7UXIe8

[rajulshah]

Hello @PBI_newuser ,

You can use LOOKUPVALUE to achieve this.

Maybe this link can help you further.

Hope this helps.