Hi I would like to have 2 columns 1. new distinct field 2. Calculated field. Explained scenario below in detail,I have a table as shown below
| Assigned Value |
Risk types |
| 16 |
CSA |
| 12 |
CSA |
| 12 |
DGI |
|
10
|
BD |
| 10 |
CC |
| 10 |
BD |
| 10 |
CA |
| 9 |
CSV |
| 9 |
CC |
| 9 |
BD |
| 8 |
DGI |
| 8 |
BD |
| 8 |
BD |
| 8 |
BD |
| 8 |
DGI |
| 8 |
DGI |
| 8 |
DGI |
| 8 |
DNC |
| 6 |
CSV |
| 6 |
EC |
| 6 |
DGI |
| 6 |
CSV |
| 6 |
DNC |
| 6 |
DNC |
| 6 |
DNC |
| 6 |
BD |
| 6 |
BD |
| 6 |
CNP |
| 6 |
CNP |
| |
|
| |
|
My first column I am looking for is a distinct values of Risk types e.g. as below without duplicates
| Risk Type |
| CSA |
| DGI |
| BD |
| CC |
| CA |
| CSV |
| DNC |
| EC |
| CNP |
2. I am looking for a calculated field parallel to the above table where
Output Formula = 𝑋_1*1+𝑋_2*1.30+𝑋_3*1.69 +𝑋_4*2.2 +𝑋_5*2.86 +𝑋_6*3.71 +𝑋_8*6.27 +𝑋_9*8.16 +𝑋_10*10.6 +𝑋_12*17.92 +𝑋_15*39.37 +𝑋_16*51.19 +𝑋_20*146.19
Where X represents assigned value in the above table.
For e.g. Risk type CSA has two assigned values i.e 16 once, 12 once which will be calculated as 1*51.19+1*17.92=69.11
Another example
For Risk type BD Assigned values are 10, 10, 9, 8, 8, 8, 6, 6 here 10 is 2 times, 9 is one time, 8 for 3 times, and 6 for 2 times. Calculation will be like 2*10.6+1*8.16+3*6.27+2*3.71 =55.59
My desired output looking for each risk types as below
| Risk type |
Output |
| CSA |
69.11 |
| BD |
55.59 |
Kindly guide and help me with the expression and suggestions. Thanks
