3 Replies Latest reply on Jul 5, 2016 11:18 AM by Tina Hauser

# Sum Dimensions based on other dimension

Hello,

I'm brand new to Tableau, and while I tried searching for this (probably easily answered) question, I wasn't able to find a decent solution.

I have a large data set of varying metrics for different countries.  All countries are assigned a continent.  For simplification's sake let's say, I have 100 rows of countries, each containing data on the number of airports, train stations, and employees.  I want to create a table where in the rows I have each continent, and in each individual column I have things like the number of countries containing airports in Asia, the total number of airports in Asia, etc.

When I add airports to my worksheet, it shows me the number of airports in each individual country, but I want to sum them all together.  I also want to be able to sum the number of countries where the number of airports > 0.

So the data looks something like this

Continent     State               Airports               Train Stations               Employees

Asia             China               2                                   4                              6

Asia             Indonesia         0                                   1                               2

Asia             Japan               3                                   5                             10

Africa           Kenya               1                                   6                               3

It currently looks like this:

Continent               Airports

Asia                              2

0

3

Africa                            1

And I want to make a table that looks like this:

Continent               Countries w/ Airports          Total Airports

Asia                                      2                                   5
Africa                                    1                                   1

TOTAL                                   3                                   6

Can anyone help?

• ###### 1. Re: Sum Dimensions based on other dimension

Hi Michael,

Use a Level of Detail (LOD) calculation to get the number of airports per continent. This sums up airports by continent, even if you have country on your worksheet:

You can also us a LOD calc determine the # of countries having an airport:

Then Analysis  --> Totals --> Column Grand Totals:

• ###### 2. Re: Sum Dimensions based on other dimension

Hi Tina,

Thanks so much.  Got it all figured out now.

I wondered if you might be able to help me with another issue I'm having, which is sorting.  I have all of my countries in rows, and measure names in columns.  Within my table, I want to show the top 5 countries for each measure value.

So let's say I have all the countries.  I want to show which 5 have the highest crime rate, and then which 5 have the highest GDP, and then which 5 have the highest population.  So each sort of 5 might have different countries, which requires the Countries data to be duplicated within the table.  I can easily sort top 5 by one measure value, but I can't seem to figure out how to create multiple sorts based on different measure values, and also display the countries next to each one.

• ###### 3. Re: Sum Dimensions based on other dimension

Happy to help. Do you have an example of how you'd like the output to look, or better yet a twbx to attach?