Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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