5 Replies Latest reply on Jul 7, 2018 1:30 PM by Farshad Rastgouy

# How do I break down a measure into a new measure of its constituent parts?

I'm working with data regarding the average salary of people in the uk.

There is one column for "Earnings" and there are other columns for other information, such as "Year", "Gender", "Geography" and "Working Pattern".

What I want to do is create a field that determines the difference in salary between the genders.

Under the column for gender it gives you the salary for men and women and so you can filter in tableau to see the salary for the different sexes separately. However, I don't know how to create a new measure that gives me the earnings for only males or only females.

If I could break down the "Earnings" measure into the earnings for men and earnings for women, I could subtract them from each other to create a field that just gives me the gender salary gap.

If anyone can help me on this, it would be greatly appreciated

• ###### 1. Re: How do I break down a measure into a new measure of its constituent parts?

You need to create a calculation like this

Sum(If [Gender] = 'Male' then [Salary] else 0 end) - Sum(If [Gender] = 'Female' then [Salary] else 0 end)

and can use this against multiple dimensions like Year , month etc.. where you can get salry difference on those levels.

Hope this helps.

• ###### 2. Re: How do I break down a measure into a new measure of its constituent parts?

Hi Jeevan,

I've created two calculated fields called "Male Earnings" and "Female Earnings" with the following calculation:

IF  [***] = 'Female' then [Earnings] ELSE NULL END

and

IF  [***] = 'Male' then [Earnings] ELSE NULL END

These work fine and generate correct values which I have checked. Then created a new field with the following calculation:

[Male Earnings]-[Female Earnings]

However when I tried to make a viz out of it, Tableau didn't register any values and just gave me nulls for everything.

• ###### 3. Re: How do I break down a measure into a new measure of its constituent parts?

Yes this happens,  because the above calculated field makes your data turns like this.

Basically when tableau encounters a calculation including NULL it returns the result of calculation as a null.

So there are 2 solutions, replacing NULL with 0 in the formulas. But this can cause issues incase you are taking aggregates like MIN or AVG.

Best way to solve this issue is to aggregate the calculations inside the calculated field, which will remove the Diff level calculations happening on row-level and change them to happen on the level of the dimension you are using in the worksheet.

Sum(If [Gender] = 'Male' then [Salary] else 0 end) - Sum(If [Gender] = 'Female' then [Salary] else 0 end)

I use the above calculation in the solution mentioned above to avoid the same issues mentioned.

Hope this helps.

1 of 1 people found this helpful
• ###### 4. Re: How do I break down a measure into a new measure of its constituent parts?

+++

You can also create 2 calculated fields like

Sum(If [Gender] = 'Male' then [Salary] else 0 end) for Male Salary

and

Sum(If [Gender] = 'Female' then [Salary] else 0 end) for Female salary and see how theye behave comapred to the calculated fields you have created to see the difference

• ###### 5. Re: How do I break down a measure into a new measure of its constituent parts?

Hi Jeeva,

Using 0 instead of NULL poses another problem for my data set.

There are over a million rows of earnings and many of them don't have an numbers inputted for the earnings, therefore all of the rows with no data will register a 0 value.

This is problematic because I have to take an average of a load of earnings, so setting all the rows with no data to 0 messes with the average.

Thanks,