10 Replies Latest reply on Jul 7, 2011 4:55 PM by Joe Mako

# create a ratio by dividing the percentage represented by two specific members of a dimension

I am trying to create a ratio by dividing the percentage represented by two specific members of a dimension – but not making much progress with the functions I am using.  Any guidance appreciated.

Group Dimension1    Level Dimension2    Ratio

Level 1          Level 2

Group1    9%            18%                          0.5

Group2    91%            82%                          1.02

Percent of the column total (the total going down the column)

Ratio = level1 percent / level 2 percent

The percentages in the cells are generated by Tableau easy enough.  I just cross the two dimensions.  I then hide Group2 because I am not interested in the result, but need it for the calculation (to generate the percentages).  What I want to do next is divide level1 percent / level 2 percent to generate the ratio.

PS. I also used IF THEN statements to break each part of the calculation into separate steps.  Each one is valid on its own, but ultimately the query fails – too complex.

• ###### 1. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

I do not understand the structure of the data you provided. Is that the data you are connecting to, or is that a crosstab Tableau has generated?

Either way, can you please attached a packaged workbook of what you have so far? (does not need to be real data, just data that represents your situation)

• ###### 2. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

Can you please attach a packaged workbook, or the data source file you used for the workbook you previously attached?

From the File-Save As dialog, you have the option to just save the Tableau workbook, without the data as a .twb file, and the option to save as a Packaged Workbook, with the data included as a .twbx file.

• ###### 3. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

Yea.  I realized that I had attached non-packaged after I hit submit.  Packages out there now.

• ###### 4. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

I still do not see a .twbx file or "Sheet1 (dummy1.xlsx).tde" or "dummy1.xlsx", any of those files would be enough in addition to your .twb file.

• ###### 5. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

Hi - Thanks for your response.

I am attaching an example of what I am trying to do in a workbook.  You'll see a 2x2 table, with tableau-calculated percentages in the four cells.  What I want to do is simply divide the result in the first cell going across by the result in the second cell going across.  Eg, for the stype dimension value 'h', divide the jlevel dimension value percent 71.23 by the jlevel dimension value percent 61.54.  The result will be something like 1.16 or so.

I tried several approaches using table calculations, which worked with the raw number of records, but not the resulting percentages that I really want to use as described above.  I also tried creating calculated values for each step, and though individually valid, they failed -- message query too complex (suspect it is something simple I am overlooking on this second approach, but cannot tell from the error message).

Appreciate any guidance here.

Thanks.

• ###### 6. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

You're right of course.  There it is.

• ###### 7. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

You're right of course.  There it is.

• ###### 8. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

how about something like the attached?

I consider this nesting table calculations, each with a different partitioning.

• ###### 9. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

These calculations helped me with a graph issue, but I'm not sure if fully understand exactly why they work.  Can you explain the role of the lookup function?

Thanks much.

• ###### 10. Re: create a ratio by dividing the percentage represented by two specific members of a dimension

If you are not familiar with Tableau's table calculation functions, I recommend the resources at http://www.tableausoftware.com/table-calculations