2 Replies Latest reply on Dec 26, 2018 1:13 AM by megha gawali

# Create a new measure from an existed measure

Hi, I have stuck with this problem for a while and cannot find a solution.

My data set looks like table below

 Name id Measure 1 Measure 2 A 1 0.29 0.01 A 2 0.77 0.86 A 3 0.73 0.51 A 4 0.76 0.80 A 5 0.06 0.36 A 6 0.03 0.64 B 1 0.76 0.07 B 2 0.38 0.49 B 3 0.62 0.71 B 4 0.98 0.89 B 5 0.16 0.71 B 6 0.39 0.79 C 1 0.97 0.48 C 2 0.09 0.55 C 3 0.31 0.76 C 4 0.64 0.35 C 5 0.98 0.65 C 6 0.52 0.71

what i want is a table like this. that's to pick the third value from measure 1 and pick the first value from measure 2 and then make them two new measures.

I knew it can be done by pivot function, but I was wondering if there a way to do the same thing by calculated field or other functions? Thanks in advance.

 Name Measure 1 when id = 3 Measure 2 when id = 1 A 0.60 0.87 B 0.17 0.36 C 0.79 0.33
• ###### 1. Re: Create a new measure from an existed measure

Hi Jiawei,

Not sure but find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

M. Measure1/2:

if [Id]=3 then [Measure 1]

elseif [Id]=1 then [Measure 2]

END

Hope it helps.

Regards,

Norbert

• ###### 2. Re: Create a new measure from an existed measure

Hello Jiawei,

You can achieve it using below calculation

1) calculation 1:  ID1

{ FIXED [Name],[Id]=1: sum ([Measure 2])}

2)calculation2: ID3

{ FIXED [Name],[Id]=3: sum ([Measure 1])}

Is this result you want

i/p used is given by you.

and this is output.