Comparing Columns

[PMF99]

Hi,

I have 2 tables in my data model & wish to calculate the difference between them

• Append_All_Boards[Board TCV] & 
• Resign_Append[Access TCV].

The tables are connected by the common field [Deal Number]. 

When I attempt to use this expression in my Append_All_Boards table =related(Resign_Append[Access TCV])...

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

Can you help?

Many thanks

Peter

[MartynRamsden]

Hi @PMF99 

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?

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[PMF99]

Thanks Martyn.

Here you go ...

[MartynRamsden]

Hi @PMF99 

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.

Can you explain in a bit more detail (ideally with some example data) what you're trying to achieve?

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[PMF99]

Hi Martyn,

Here's the pivot I want to create....

[Board TCV] by [Deal Code] comes from Append_All Boards and gives the correct answer.

[Access TCV] by [Deal Code] comes from Resign_Appends and gives the correct answer.

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.

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.

Many thanks

Peter

[MartynRamsden]

Hi @PMF99 

In that case, I would advise that you create 2 explicit measures to calculate the sum of [Board TCV] and [Access TCV], as follows:

Total Board TCV = SUM ( Append_All Boards[Board TCV] )

Total Access TCV = SUM ( Resign_Appends[Access TCV] )

You can then use those measure within another measure to calcualte the variance.

Variance = [Total Board TCV] - [Total Access TCV]

You can then add all 3 measures to your pivot table to give you the result you want.

Hope this helps.

Best regards,

Martyn

[PMF99]

Thanks Martyn.

But it needs to be by [Deal Code]. Will your suggestion achieve this?

Thanks

Peter

[MartynRamsden]

Hi @PMF99 

You'll need to include the [Deal Code] column from the 'Append_All_Boards' table on the rows of your pivot table.

The measures should then be calculated under the correct filter context.

If you have any trouble with it, please share a copy of your file (excluding any sensitive data).

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.