Forum Discussion

owenmdavies's avatar
owenmdavies
Frequent Visitor
9 years ago
Solved

Custom column/measure based on value in table

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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