4 Replies Latest reply on Nov 21, 2012 10:26 PM by Ram Hardikar

    Aggregation in Group

    Ram Hardikar

      Hi,

       

      I have created a group by the name 'All Others' which shows overlapping values, probably because the values are not summed up. Appreciate if someone could let me know how to aggregate values within a group.

       

      P.S. Screenshot attached

        • 1. Re: Aggregation in Group
          Tracy Rodgers

          Hi Ram,

           

          This is happening because there are multiple dimension members in Spending that match up All Others. There are a couple of options that come to mind. One is to use a table calculation that pulls out the highest value in that group, similar to the following:

           

          lookup(max([Spending]), last())

           

          However, my guess is that this won't give you the result that you want--which is probably a sum of all the dimension members in the All Other Department. In which case, I would recommend, changing Spending from a dimension to a measure. You can do this by creating 2 calculations similar to the following:

           

          1. REPLACE( [Spending], "M", "000")

           

          2. int(mid([Above calculation], 2))

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Aggregation in Group
            Ram Hardikar

            Hi Tracy,

             

            This definitely helps to convert a Dim element to a Measure. But I am still confused how to use this to show the group aggregation along with the numbers on the other rows.

            If I use this calculated field then I lose numbers for other rows and if I don’t use this calculation then I back to my original problem. Surely I am missing something. Appreciate your help here.

             

            I have attached a screenshot of the calculated field in use.

            • 3. Re: Aggregation in Group
              Ram Hardikar

              I also want to highlight that numbers in the excel are of the format '$11,804M'. Not sure if this is causing the numbers not to sum up??

              • 4. Re: Aggregation in Group
                Ram Hardikar

                This is what I tried and the aggregation worked for the group -

                 

                In the spreadsheet, I stripped the '$' & 'M' chars from the string and converted it to number. Now when I used this updated spreadsheet, I was able to get the aggregation for the grouped members. So long as the underlying data is numeric the aggregation happens automatically.

                 

                But I have a feeling that there could be another way to handle this directly in tableau without the need for editing the data. Any suggestions to achieve that are much appreciated.