Using parameter table to make new column

[Anonymous]

Hi,

I have a table Booking imported from an Excel file

ID	Code	Memberschip	Installment	Commission
1	B	86,94	60,00	250,00
2	C	19,44	255,00	750,00
3	B	7,86	705,00	2000,00
4	C	33,00	75,00	240,00
5	C	60,04	120,00	390,00
6	C	78,44	420,00	1300,00
7	A	7,46	615,00	1700,00
8	A	67,16	105,00	350,00
9	C	38,10	30,00	120,00
10	C	97,44	120,00	420,00
11	C	65,04	180,00	550,00
12	A	2,95	105,00	286,00
13	C	65,59	255,00	760,00

 
I want to add a new column Report_Commission using a table Commission_Parameters I created by using "Enter data": 

Code	Tax%	Commision%
A	0,0925	0,25

 The two tables are linked:

To add the new column, I use "New Column" in the Home tab

Report_Commission = IF(Booking[Code]="A";Booking[Memberschip]+Booking[Installment]*0,25/0,0925;Booking[Commission])

Question 1: Why can't I find the table Commission_Parameters in the autocompletion?

Question 2:

When I explicitly add the two fields from Commission_Parameters:

Report_Commission = IF(Booking[Code]="A"; Booking[Memberschip] + Booking[Installment] * Commission_Parameters[Commission%] / Commission_Parameters[Tax%]; Booking[Commission])

why do I get the error:  "A single value for column 'Commission%' in table 'Commission_Parameters' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."?

Question 3: Why does this error persist even when I add an aggregation to the fields Commission_Parameters[Commission%] and Commission_Parameters[Tax%]?

Thanks,

R.W.

[DaFloDo]

hi,

you could also join the information as additional columns using power query - I like to do this for these cases

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

 

best regards,

florian

[HotChilli · Accepted solution]

The answers to almost all of your questions are to do with how you link to the other table from the 'many' side.

You have to use the RELATED function.  The intellisense (autocompletion) is smart enough to give you only items that it thinks you can use according to the template of the functions you call.

[Anonymous]

Hi HotChilli,

Thanks, this works

Report_Commission = ROUND(IF(Booking[Code]="A"; Booking[Memberschip] + Booking[Installment] * RELATED(Commission_Parameters[Commission%]) / RELATED(Commission_Parameters[Tax%]); Booking[Commission]);2)

R.W.