Forum Discussion

superbird's avatar
superbird
Frequent Visitor
3 years ago
Solved

Linking Measure to Column in another Table

Hi there,   I want to be able to link a Measure (Average of Column, round to nearest 1000) to a Column in another table.   Table 1  Product Value A 1000 B 1245 C 1300 D 800 ...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi superbird 

     

    Divide the average by a thousand and round it off to the nearest integer and then multiply it by 1000. Use that as lookup value.

     

    AverageThousands =
LOOKUPVALUE (
    Table2[Output],
    Table2[Value], ROUND ( DIVIDE ( [Average], 1000 ), 0 ) * 1000
)

    Value in table2 should always be unique for the lookup to work correctly.

  • superbird's avatar
    superbird
    3 years ago

    thanks so much, that works 🙂
    what if i have multiple output columns in table 2 - would i need to create a lookupvalue for each column? there is no way to create a relationship to save having to create multiple lookupvalues?

superbird's avatar
superbird
Frequent Visitor
3 years ago

thanks so much, that works 🙂
what if i have multiple output columns in table 2 - would i need to create a lookupvalue for each column? there is no way to create a relationship to save having to create multiple lookupvalues?

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi superbird ,

     

    Relationships will not work as you said that your average value is dynamic. You would have to use a calculated column to create a relationship that means  the average won't be dynamic anymore.

    LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

    As you can see, LOOKUPVALUE accepts more than one search column and search value but only one result column. If you want your formula to point to another result column, say to Output2 column, creating another measure/column is necessary.