Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
I reviewed the relationship between the two tables and discovered that it had been set up incorrectly.
Thank you, it is working now!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |