Forum Discussion

RossCurrie15's avatar
RossCurrie15
Regular Visitor
9 years ago

Basic variance between two columns.

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 val...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
    RossCurrie15
    Regular Visitor
    9 years ago

    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

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      Hi RossCurrie15,

       

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

       

      Best Regards,
      Qiuyun Yu

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      RossCurrie15

       

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