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

    Sum Dimensions based on other dimension




      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



      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
          Tina Hauser

          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
              Tina Hauser

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