4 Replies Latest reply on Oct 14, 2016 6:31 PM by Benjamin Cole

# Calculated Field to Transform Data?

Hi Everyone!

I'm exploring this "World bank Data" data set found here.  I've already pivoted the data once in order to set up the years correctly.

The Setup

Below you can see a screen shot showing the dimension Series_Name, that segments GDP and Population.

I'd like to show GDP and Population on the same graph, but because they are on such different scales I need to do a dual axis graph.

The Question

How do I convert the dimension Series_Name into multiple measures, GPD and Population?

Case Statement?

THANKS!

Ben • ###### 1. Re: Calculated Field to Transform Data?

hi Benjamin,

So you can do this with the following 2 calculations;

[GDP]

IIF([Series name] = '"GDP (\$)",[Value],0)

[Population]

IIF([Series name] = '"Population",[Value],0)

When you bring these in, say with the aggregate SUM each field only contains the SUM of each measure.

Let me know if that doesn't work, or doesn't make sense.

• ###### 2. Re: Calculated Field to Transform Data?

Hello Benjamin,

Use case statement as below.

Case when [series name] = 'GDP' then [value]

when [series name] = 'Population' then  [value] else [series name] end

• ###### 3. Re: Calculated Field to Transform Data?

Ahh yes, this worked...thanks!

• ###### 4. Re: Calculated Field to Transform Data?

Hey Ravindra,

Thanks for the case statement, it didn't really address the issue because its still reliant on using the Series name and doesn't provide new measure values that i could then build a dual axis graph with.