cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RossCurrie15 Frequent Visitor
Frequent Visitor

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 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.

5 REPLIES 5
BhaveshPatel Super Contributor
Super Contributor

Re: Basic variance between two columns.

@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.

 

variance.PNG

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Moderator v-qiuyu-msft
Moderator

Re: Basic variance between two columns.

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])

 

z1.PNGz2.PNGz3.PNGz4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
RossCurrie15 Frequent Visitor
Frequent Visitor

Re: Basic variance between two columns.

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

Moderator v-qiuyu-msft
Moderator

Re: Basic variance between two columns.

Hi @RossCurrie15,

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Vvelarde Super Contributor
Super Contributor

Re: Basic variance between two columns.

@RossCurrie15

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors