Forum Discussion
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 |
| E | 1400 |
Measure1 = Average of Value = 1,149
to nearest 1000 = 1,000
Table 2
| Value | Output |
| 1000 | Thousand |
| 2000 | Two Thousand |
| 3000 | Three Thousand |
| 4000 | Four Thousand |
| 5000 | Five Thousand |
I want to link Measure1 (1,000) to Value Column (1,000) in Table 2, so that Output generated is 'Thousand' as per table above.
This needs to be dynamic because Measure1 may change depending on the Average generated.
What is easiest way to do this?
Thanks,
Phil
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.
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?
3 Replies
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.
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?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.