Custom column/measure based on value in table

[owenmdavies]

Hi

I have a table like this

The data for actual will be from another table and keyed on the Product/Colour

What is the best method to calculate the actual ?

Thanks in advance

[Anonymous · Accepted solution]

Hi @owenmdavies,

If the target table only has one match record, I think lookupvalue should be simple to get result.

If it has multiple records can match the conditions, you can try to sumx function.

Sample:

Calculate column
Single= Lookupvalue(Target[Actual],Target[Product], Product[Product],Target[Color],Product[Color])
Total= SUMX(FILTER(ALL(Target),Target[Product]=EARLIER(Product[Product])&&Target[Color]=EARLIER(Product[Color])),[Actual])

Regards,

Xiaoxin Sheng