Lookupvalue with filter or alternative Formula

[Anonymous]

Hi Experts

I want to look up the Value in myAppend_Direct Ohds_IAM&OES  Where Cost Type = "11.S&M". and mulitple this value by my calculated column which is in my sales table (4.Purchases)

Both Append_Direct Ohds_IAM&OES Table and Sales FACT have the following same column

Sales Data Master FACT

Month

Year

Channel

Country

Append_Direct Ohds_IAM&OES

Month

Year

Channel

Country

Cost Type

SP Cannot use related table.....

[Anonymous · Accepted solution]

LookupCost = LOOKUPVALUE( 'Append_Direct Ohds_IAM&OES'[Value], 'Append_Direct Ohds_IAM&OES'[Month], Sales_Data_Master[MonthV2], 'Append_Direct Ohds_IAM&OES'[Year], Sales_Data_Master[Years_In_Date], 'Append_Direct Ohds_IAM&OES'[Channel], Sales_Data_Master[Channel], 'Append_Direct Ohds_IAM&OES'[Country], Sales_Data_Master[Sales_Country], 'Append_Direct Ohds_IAM&OES'[Type of Cost], "4.Purch")

[Anonymous]

Sample Data

https://www.dropbox.com/s/727dslw29huny80/Test.pbix?dl=0