Return value from table based on measure value and x2 relationship columns

[WESTi]

I have one table like this:

Area | Location | Value

and anther table like this:

Area | Location | Value | Worth

What I want to do is return the worth value. However, I cannot create a relationship as I need to lookup the SUM of the value from the first table. For example:

I may have the following data set

Area | Location | Value

AK | NZ | 10

AK | NZ | 5

AK | AU | 4

What I need to do is not return the worth on each row - so I don't want to look up the 10 and 5 for the NZ location, instead I want to lookup 15 (the SUM of the values). The end table I want to create would look like this - it would be a summary:

AK | NZ | 15 | 500

Does anyone have any ideas how I could achieve this?

[v-yulgu-msft · Accepted solution]

Hi @WESTi,

In this scenario, you can first summarize your source table.

NewTable1 =
SUMMARIZE (
    'Return Value Table1',
    'Return Value Table1'[Area],
    'Return Value Table1'[Location],
    "Total Value", SUM ( 'Return Value Table1'[Value] )
)

In 'NewTable1', get the worth value from second table via LookUpValue.

Return Worth =
LOOKUPVALUE (
    'Return Value Table2'[Worth],
    'Return Value Table2'[Area], NewTable1[Area],
    'Return Value Table2'[Location], NewTable1[Location],
    'Return Value Table2'[Value], NewTable1[Total Value]
)

Best regards,
Yuliana Gu

[Anonymous]

Can you clarify if you are hoping to add a calc column to 1 of your table, create a new table, or are we talking about just creating measures and visuals?