Applying a Calculated Column if a Column from Another Table Equals a Specific Value

[mitchperkins]

I have two tables. 'Table A' holds general opportunity information, and 'Table B' holds specific information about the opportunities. The table relationship is formed on the Opportunity_ID.

We have different commission calculations for different sale types. I have created each commission calculation as calculated columns in Table A, using other Table A columns. Ex. 'Table A'[Commission_Agreement Renewal] = ([cycles]/12)*[Recurring_Revenue].

Table B has a column with the sale type information ([Sale Type] = Agreement Renewal).

I need to create a column in Table A that can provide the sale commission dependent on the sale type. My intital reaction was to write a nested IF statement that was along the lines of:
Commission = IF ( RELATED ( 'Table B'[Sale Type] ) = Agreement Renewal, [Commission_Agreement Renewal],

IF ( RELATED ( 'Table B'[Sale Type] ) = New RMR System, [Commission_New RMR System],

(pretend there are 5 or so more lines of sale types following the same format),

0)

I receive errors about not finding a single value for [Sale Type] most times that I try it, but no matter how I attempt to get around it, I seem to receive another error.

Any ideas as to how I would accomplish this?

[selimovd · Accepted solution]

Hey @mitchperkins ,

can you also share the relationship of the two tables?

Usually a RELATED gives back a single value, but as you said there is an error I would like to see the relationship.

Did you break the formula down to maybe only one IF and see if that works? I would do that before creating a very long IF-statement.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[mitchperkins]

I reviewed the relationship between the two tables and discovered that it had been set up incorrectly.

Thank you, it is working now!