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

    Sum Dimensions based on other dimension

    michael.baicker

      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
          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
            michael.baicker

            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?