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:
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!
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.
Calculated Field.JPG 94.3 KB
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??
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.