Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using parameter table to make new column

Hi,

 

I have a table Booking imported from an Excel file

 

IDCodeMemberschipInstallmentCommission
1B86,9460,00250,00
2C19,44255,00750,00
3B7,86705,002000,00
4C33,0075,00240,00
5C60,04120,00390,00
6C78,44420,001300,00
7A7,46615,001700,00
8A67,16105,00350,00
9C38,1030,00120,00
10C97,44120,00420,00
11C65,04180,00550,00
12A2,95105,00286,00
13C65,59255,00760,00


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

 

CodeTax%Commision%
A0,09250,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.

 

 

 

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

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      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.