11 Replies Latest reply on Jan 26, 2017 7:52 AM by Amrita Jain


    Amrita Jain

      Hi everyone!


      I am trying to build a dashboard which basically gives selected census information for all counties in a state. My data is at the county level but I also want to be able to show the data at the state level. Now the issue is when I aggregate  this data the default aggregation is taking the sum for each measure.. I want to be able to aggregate depending on the measure, for example if its distance to work then its an average of all counties rather than a sum. I am struggling with how to create a sheet that will have all of the state level measures aggregated in a sensible way. I am then using parameters and action filters to link it to a map of the state in the dashboard.. so when you click on the map you will see the measures. Additionally, I have also divided the census information in 5 categories that one can choose from a drop down menu. I am using Tableau 10.

      Any help will be greatly appreciated!

        • 1. Re: Aggregation
          Joe Oppelt

          Post a sample workbook.  And the version of Tableau you are using.  There are too many moving parts to make suggestions without doing it in the context of the sheet design and data layout you have.

          • 2. Re: Aggregation
            Amrita Jain

            Hi Joe,


            Thank you for your message.


            This is the link to the worksbook https://10az.online.tableau.com/#/site/amritavjain/views/2017/Dashboard1?:iid=1


            I am using Tableau 10.



            • 3. Re: Aggregation
              Joe Oppelt

              I couldn't log into that.

              Extract it and upload it here.

              • 4. Re: Aggregation
                Amrita Jain

                here you go!

                • 5. Re: Aggregation
                  Joe Oppelt

                  OK, I see what's going on.


                  Because you have only one pill (  SUM(Values)  ) on the text shelf, you're only going to get one type of aggregation:  SUM.  That's all it can be.


                  So I'm just thinking out loud here...  You'll need a calc of some sort to collect different types of aggregations based on the [Measures] in question.


                  Can you give me two scenarios that you might want to do?  One that will do sums, and one that will do averages.  Specifically, what would the user select to cause the need for an average of Median Distances, and conversely, what would cause a sum of "Something else".  When I understand better what could happen, I can figure out if we want table calcs or LODs to do the job.


                  Once I can hack up a calc that will capture the proper aggregations, we can do AGG([That Calc]) instead of SUM([Values]) on the text shelf.

                  1 of 1 people found this helpful
                  • 6. Re: Aggregation
                    Amrita Jain

                    Yes, you got that right.


                    So when a user selects "State" in the "Select a region" drop down and then clicks on the map of MN, you will see the measure table on the right in the dashboard will change and currently it just gives all the counties, not an aggregated state number. I would need a "Sum" calculation when the user selects a measure category from the drop down that might have fields like total population or total households. On the other hand, if they select Housing and Homelessness and want to know "Median Gross Rent" for the state, then they would want to look at an average value. So I feel like different measures will either need a sum, or average or even percent of total calculations.


                    I can make a lists of measure fields that need an aggregation or sum or percent of total, if that will help?


                    Do you think since I have grouped the measures it will make it difficult to do separate calculations?

                    • 7. Re: Aggregation
                      Joe Oppelt

                      The grouping might complicate things, but I think it's a clever way to do what you are trying to do and I don't want to abandon that.

                      Let me look at this.

                      • 8. Re: Aggregation
                        Amrita Jain

                        Thanks a lot!! I really appreciate the help!

                        • 9. Re: Aggregation
                          Joe Oppelt

                          Take a look at what I did here.


                          (See Sheet 6)


                          First of all, I moved Median number of miles into the Number of veterans group just to have two things to compare at one time.  It probably doesn't make sense for your end goal, but it lets me mess with two "competing" measures together.


                          First thing I did was change the hierarchy to start with State.  And then I put STATE on filters and excluded null.


                          As a test I created two calcs:  [Number of veterans aggregate...]  One is a table calc, and one is LOD.  I display them in the title for testing purposes.


                          Both work correctly.  And since they do, we're better off going with LOD here so that we don't have to mess with the addressing directions of the table calc.


                          So next I created a calc called Calculated Values.  This is the calc I was referring to earlier.  Take a look at that.  I do different LOD expressions (like I did in the [Number of veterans LOD]) for each measure.  One is a SUM.  One is AVG.  You can do the same for all the different measures you want to mess with.

                          Notice the display on Sheet 6.  The old [Values] sum changes as the hierarchy is expanded.  But the calc value doesn't change.  This isn't the end of the game here.  I just wanted to show you how you can manipulate your data.


                          Keep expanding your hierarchy to [Tribal Nations].  You'll notice that the LOD calc no longer looks right.  That's because it currently doesn't take into account [Tribal Nations].  You'll need to add that in the "include" list in the table calcs.


                          And if you want to see the numbers roll up to the CoC Region level instead of STATE, you would take that variable OFF the INCLUDE list.  (So yes, you would need separate calcs to show the various levels.  AND you would need separate sheets to show the various crosstabs, just like you are doing with the map sheets, and swap them accordingly like you are doing for the map sheets.)  So really you would have a [Calculated value -- State] and a [Calculated Value -- CoC], etc.  Right now what I made is rolling up to the state level.  And it works until [Tribal] is exposed on the sheet because I have addressed all the dimensions that are always on the sheet (CoC and County by virtue of the filters, and State because it's always in the ROWS on this sheet.)


                          I hope this gets you aimed in the right direction.  As you expand this out, ping back if you run into hurdles.


                          And if I'm not mistaken, you shouldn't need different sets of calcs based on which set the user selects.  One giant calc for all the measures should do it all, no matter which set the user chooses.

                          1 of 1 people found this helpful
                          • 10. Re: Aggregation
                            Amrita Jain

                            Thanks a lot Joe!!! I am implementing the solution you suggested.. I was not familiar with LOD so trying to make sure I get all the calculations right. I will get back to you in a day to share progress.


                            Thanks a ton!!

                            • 11. Re: Aggregation
                              Amrita Jain

                              Hi Joe,


                              Your solution worked perfectly! I made separate sheets for CoC and State and aggregated it using the If .. then and LOD formula that you had put together. Then I linked it the same way I had done it for the maps..Thank you SO MUCH!!!! It works like magic