Hi I would like to add 2 columns where one is new calculated field (column) and another one is distinct risk types of an existing risk type in the table.
Here is my scenario , Existing risk type column
| 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 |
| 6 |
CNP |
| 6 |
CNP |
First column output expected like shown below where there is no duplicates of risk types
| Risk Type |
| CSA |
| DGI |
| BD |
| CC |
| CA |
| CSV |
| DNC |
| EC |
| CNP |
Where my formula for 2nd column calculated field is like
Risk type= X_1 (1)+X_2 (1.30)+X_3 (1.69) +X_4 (2.2) +X_5 (2.86) +X_6 (3.71) +X_8 (6.27) +X_9 (8.16) +X_10 (10.6) +X_12(17.92) +X_15 (39.37) +X_16 (51.19) +X_20 (146.19)
Where X1 is Assigned Value X1 = 1, X2 = 2, X3=3, X16= 16 etc.. In Risk type column for a assigned value e.g. if X = CSA and in the above scenario in Risk type column it is repeated twice where assigned value for CSA is X=16 and X=12 it will be calculated as 1*51.19 + 1*17.92 = 69.11
Another example for Risk type DGI where Assigned Values are X12 = 1, X8 =4 times, X6 = 1 time so the calculation will be like 1*17.92+4*6.27+1*3.71 = 46.71
Expected output columns will be like
| Risk Types |
Output |
| CSA |
69.11 |
| DGI |
46.71 |
Kindly guide and support, Thanks
