Relationship, SUMIF, VLOOKUP and SUBTRACTION

[Anonymous]

I need some help here as a Power BI beginner. We have 2 sets of data 'Actual' and 'Forecast'. Desired outcome is to calculate the variance between 'Actual' and 'Forecast'. Assuming it is Excel, Step 1 - Identifier is [Subregion]&[BU]&[SpendPoolLow] Could not create relationship between 2 identifiers due to duplicated rows in a file and each other contains identifier not existing in the other file. How to fix this in Power BI? Step 2 - SUMIF based on identifier in both files, and VLOOKUP Step 3 - Manually add in missing identifier in the outcome table, pull down SUMIF function for Actual, VLOOKUP the Forecast value again Step 4 - Subtract Actual from Forecast to calculate variance How to write DAX Step 2-4?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated table as a bridge table to have a try.

Table = DISTINCT(UNION(VALUES(Actual[Identifier]),VALUES(Forecast[Identifier])))

Then we can create relationships like this.

[Anonymous]

Screenshot of error on relationship.

Attaching the Power BI file... Truly appreciate your help!! Thanks in advanced.

https://drive.google.com/drive/folders/1F0Bu3ZXyNXdhNhJ6AC-elk3YzxmgkHYv?usp=sharing

Regards,

Joyce