Comparing Columns

Question

Hi,&nbsp;I have 2 tables in my data model &amp; wish to calculate the difference between themAppend_All_Boards[Board TCV] &amp;&nbsp;Resign_Append[Access TCV].The tables are connected by the common field&nbsp;[Deal Number].&nbsp;&nbsp;When I attempt to use this expression in my Append_All_Boards table =related(Resign_Append[Access TCV])...&nbsp;I get the following error message.."The column 'Resign_Append[Access TCV]' either doesn't exist or doesn't have a relationship to any table available in the current context."&nbsp;Can you help?&nbsp;Many thanksPeter&nbsp;&nbsp;&nbsp;

martynramsden · Answer

Hi PMF99&nbsp;&nbsp;Can you confirm that you have an active many to one relationship between Append_All_Boards and Resign_Append?Are you able to include a snip / screen shot of your Relationship view?&nbsp;Best regards,Martyn&nbsp;If I answered your question, please help others by accepting it as a solution.

pmf99 · Answer

Thanks Martyn.&nbsp;Here you go ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

martynramsden · Answer

Hi PMF99&nbsp;&nbsp;The RELATED function can only be used on the many side of a many to one relationship.In your case, this is the Resign_Append table. Hence why you're seeing the error.&nbsp;Can you explain in a bit more detail (ideally with some example data) what you're trying to achieve?Best regards,Martyn&nbsp;If I answered your question, please help others by accepting it as a solution.

pmf99 · Answer

Hi Martyn,&nbsp;Here's the pivot I want to create....&nbsp;[Board TCV] by [Deal Code] comes from Append_All Boards and gives the correct answer.&nbsp;[Access TCV] by [Deal Code] comes from Resign_Appends and gives the correct answer.&nbsp;My issue is I'd like to calculate the Var (variance) in Power Pivot and not manually calculate in Excel as I'm now doing.&nbsp;Trying to bring the 2 columns together in 1 table to be able to calculate the variance in Power Pivot is what's giving me the problem.&nbsp;Many thanksPeter&nbsp;&nbsp;

martynramsden · Answer

Hi PMF99&nbsp;&nbsp;In that case, I would advise that you create 2 explicit measures to calculate the sum of [Board TCV] and [Access TCV], as follows:&nbsp;Total Board TCV = SUM ( Append_All Boards[Board TCV] )Total Access TCV = SUM ( Resign_Appends[Access TCV] )&nbsp;You can then use those measure within another measure to calcualte the variance.Variance = [Total Board TCV] - [Total Access TCV]&nbsp;You can then add all 3 measures to your pivot table to give you the result you want.&nbsp;Hope this helps.&nbsp;Best regards,Martyn&nbsp;&nbsp;

Forum Discussion

Comparing Columns

7 Replies

Related content

Compare Dynamic Columns in a Matrix

Compare two columns in different tables

Comparing columns error

Comparing two tables columns by columns

Table Sort , sort order , comparer