3 Replies Latest reply on Feb 8, 2019 7:14 AM by Gerardo Varela

# top n with "other" sum for arbitrary dimension depth

Hi everyone! I'm working on creating a dynamic table that shows, for an arbitrary depth of dimensions, the top N items for each dimension and an "other" sub total that sums up the rest. I've worked through a few of the posted solutions for similar questions and I'm having trouble aggregating others past the first dimension.

I've attached my most successful attempt. This is my first project in Tableau and I suspect I'm committing great acts of abuse... I'm calculating "other" subtotals for both dimensions for each row and then doing some hacky field renaming and conditional calculations for display. That said, everything in the "2 dimensions" worksheet is as desired except the label for dimension 2 in the dimension 1 "others" subtotal is not displaying "others" as well. If anyone could take a look and advise, it'd be greatly appreciated. Extra appreciation for any solutions that can be extended to work for 3, or 4, or 5 dimensions as well. Many thanks!

• ###### 1. Re: top n with "other" sum for arbitrary dimension depth

Andrew - this is a good challenge and you have done well so far so thank you for bringing this to the community with a workbook asking for assistance.  I have been playing with this a bit and have not made headway so I am asking a few of our Ambassadors to see if they might have an idea about how to tackle.  This would be a good post to hold for future multi level dimension ranking/sorting to help others.

Jonathan Drummey

• ###### 2. Re: top n with "other" sum for arbitrary dimension depth

Andrew

I changed your Dimension 2 Collapsed calculated field to

if [Parameter 2] = 'None' then NULL

elseif [Dimension 1 Collapsed] = 'others' then 'others'

elseif [d2 rank] > [Top Parameter 2] then "others"

else max([Dimension 2])

end

that seems to have solved your labelling/display issue if I've understood you correctly....

Donna

• ###### 3. Re: top n with "other" sum for arbitrary dimension depth

If I'm understanding what you are looking for, edit the table calculation Dimension 2 Collapsed.  Set the partition of d1 rank to compute over Dimension 1 and Dimension 2.  It's currently set to table across.

Attached workbook version 2018.1.

Regards,

Gerardo