Solved: Re: Relationship, SUMIF, VLOOKUP and SUBTRACTION

Anonymous · ‎08-05-2019

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 · ‎08-05-2019

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

v-frfei-msft · ‎08-05-2019

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.