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

# Aggregation in Group

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

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

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

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

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.