New calculated field Expression

[Venkatsj]

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

[G3S]

you will have to load a table of assigned value and the factor.

Assigned Value	Factor
16	51.19
12	17.92

have it joined to your fact table. summing up the Factor per  risk type should give you the output.