How to create a measure between two related tables?

[mdrammeh]

Hello Everyone,

I am very new with Power BI/ Power Query and seeking your help to resolve this scenario:

Scenario:

I have a set of tables from two companies. Each company has a different process but their data is structured in the same format. For each company, I have been given a total spend and target by zone, location, employee, date etc.. in two separate tables.

The goal here is to combine the two tables into a single table to create a relationship. The table with the spend has multiple transactions repeated overtime for each company. The targets are grouped by zone, location, employee, date etc.. in two separate tables but only once for each row. In short, I have one table with MANY transactions and the other table with ONLY ONE transaction for each row.

Therefore, I need to create a relationship between the two tables so that I can calculate the variance between the sum of all spend by zone, location, employee, date etc..

I have tried to append the two queries together but for whatever reason, some of the values are being duplicated. Also, when I tried to force a relationship between the two tables but I keep getting an error message.

Could someone walk me through how to resolve this issue in Power BI and Power Query?

Thanks for all your help in advance.

Drapatua

[v-sihou-msft · Accepted solution]

@mdrammeh

In Tabular model, we can't build relationship between two tables with composite keys. In this scenario, I suggest you create a lookup column in your spend table (many side table).

Target Value =
LOOKUPVALUE (
    Target[Target Dollars],
    Spend[Country], Target[Country],
    Spend[Zone], Target[Zone],
    Spend[Location], Target[Location],
    Spend[Cost Category], Target[Cost Category],
    Spend[Date], Target[Date]
)

Then you can create a calculated table to SUMMARIZE Spend and Target on Zone, Country, Location, Category and Date Level.

Table =
SUMMARIZE (
    Spend,
    Spend[Country],
    Spend[Zone],
    Spend[Location],
    Spend[Cost Category],
    Spend[Date],
    "Spend Total", SUM ( Spend[YTD$Spend] ),
    "Target", MAX ( Spend[Target Value] )
)

Regards,