Linking Measure to Column in another Table

[superbird]

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

[danextian · Accepted solution]

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 · Accepted solution]

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]

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.

[danextian · Accepted solution]

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.