5 Replies Latest reply on Nov 6, 2015 6:22 AM by Junhe Yang

# Percentage calculation within a dimension

Hello community,

I have a data structure like below:

 Indicator Value Year A 1 1990 B 2 1990 C 3 1990 D 4 1990 A 5 1991 B 6 1991 C 7 1991 D 8 1991 A 9 1992 B 10 1992 C 11 1992 D 12 1992

In tableau I would like to calculate the result of A/B over all years., i.e. 1990, 1991 and 1992. So that for 1990, the result will be 1/2 = 50%, for 1991, the result is 5/6, and for 1992, the result will be 9/10=90%.

I tried with table calculation but it seems hard to get the desired result. Could anyone help with this tricky problem? Many thanks in advance. A sample tableau file attached.

June

• ###### 1. Re: Percentage calculation within a dimension

Junhe,

How about a calculation like this:

SUM(IF [Indicator] = 'A' THEN [Value] ELSE 0 END)

/

SUM(IF [Indicator] = 'B' THEN [Value] ELSE 0 END)

That should give you the results you want:

Best Regards,

Joshua

• ###### 2. Re: Percentage calculation within a dimension

And I'd definitely be happy to provide any additional explanation or work around any complications you might have!

• ###### 3. Re: Percentage calculation within a dimension

Thank you so much Joshua. This solves my problem! However I am still a little bit confused why we used "SUM" here? Does the function takes sum by year, or indicator? Many thanks!

• ###### 4. Re: Percentage calculation within a dimension

Junhe,

The SUM allows it to be calculated at an aggregate level instead of row by row.  The reason that is important is that if it was calculated row by row, then only the numerator or denominator of the calculation would be valid for any row (and possibly neither would be valid).

If you did not use SUM:

(IF [Indicator] = 'A' THEN [Value] ELSE 0 END)

/

(IF [Indicator] = 'B' THEN [Value] ELSE 0 END)

Then the row

A     1     1990

would end up getting calculated like this:

1 / 0 = NULL

(because the indicator was A and not B for that row)

and the row

B     2     1990

would end up like this:

0 / 2 = 0

(because the indicator was B and not A for that row)

So, you would never get a meaningful answer on any row and you wouldn't be able to aggregate over the years.  But, applying the SUM allows it to sum up all the A rows for the numerator and all the B rows for the denominator (split up by Year -- or any other dimension you choose).

Best Regards,

Joshua

1 of 1 people found this helpful
• ###### 5. Re: Percentage calculation within a dimension

Thank you so much Joshua. You've been great help! I now understand the aggregate functions.

Best regards,

Junhe