Basic variance between two columns.

[RossCurrie15]

Hi Power BI Experts,

Loaded up two data sets, both identical in fields with exception of one columns values.

Same as how you would do in excel, I would like to do a column with a variance values 1 to values 2 to understand the difference between the two?

Tried creating a column, measure, relationship etc but receiving multiple errors?

Should be a simple = data1 column x - data 2 column x? No?

Thanks for help.

[v-qiuyu-msft]

Hi @RossCurrie15,

If I understand correctly, you created two tables in desktop, and there is a relationship between those two tables, you want to calculate difference between columns within two tables, right?

Assume create a relationship between two tables based on Item field, if you create a calculated column in Table1, you need to use Related() function to return value from Table2. See:

Diff = Table1[Sales1]-RELATED(Table2[Sales2])

Best Regards,
Qiuyun Yu

[RossCurrie15]

Hi Qinyun,

thank you for your explanation and i understand the rationale however when i try to create a relationship between the data, i receive an error message "Missing intermediate data between the two columns"? Yet i can't understand why this would be, both tables have different values in them?

Thanks,

Ross

[Vvelarde]

@RossCurrie15

You have to check the columns that are you trying to related. Maybe someone is different (Data, Format or another thing) to other.

[v-qiuyu-msft]

Hi @RossCurrie15,

Would you please share the sample data about those two tables? If possible, please share the .PBIX file.

Best Regards,
Qiuyun Yu

[BhaveshPatel]

@RossCurrie15

You should create a new column and as shown in the screenshot, Type in the formula. 

I still do not understand your "No" part of the formula. If can be explained, can provide you a  solution.