5 Replies Latest reply on Feb 23, 2015 9:50 AM by james.diaz

# Roll up a dimension based on a % of a measure

Hi -

I am trying to do something very simple. I want to roll up a dimension such as "Customer" based on it's size compared to other customers. In the attached, I am trying to roll up Customers Small 1,2,3 as "All Other" based on the fact that they are less than 0.1% of total sales

• I do NOT want to use sets, as they are very limited, though I know I could use one in the simple example I have attached. I want to use a calculated field
• I cannot just select a "Top N" customers in my data set, even though that would work in this data set
• Basically, because I am using a calculation, Tableau assumes that I want another Measure
• My company provides Tableau 8.1 as of right now...

Thanks in advance for the community's help!

Jake

• ###### 1. Re: Roll up a dimension based on a % of a measure

Hi,

The Following Example is showing Cities If % of total is Less than 10 then "Other" else [City].

I have created a calculated field to get the % of total.

sum([Sales]) / window_sum(sum([Sales]))*100

Then I create following Calculated field to get the Customized Field.

if [Sales % of total]<10.00 then attr("Other") else attr([City]) end

Expected Result:

• ###### 2. Re: Roll up a dimension based on a % of a measure

Hi Jake,

You hit a nail with this seemingly "simple" task.

NOT using sets you've left with a decent choice,

which is using mainly table calculations.

Though basic calculations looks rather simple both ways,

but the table calc approach needs a mastery when dealing with Totals.

Yep, you got it correct. To get as simple as 100% Grand Total,

one need to write an overly complicated conditional logic into a table calc.

Moreover, trying to accomplish this, I used TD version 8.3.2

Then I simply re-write my calcs into version 8.1.16 workbook, and guess what?

Grand Totals again became (and stay) as messy as before :-(

So I attach both 8.1.16 and 8.3.2 versions here.

If anybody interest in the subject, please welcome :-)

Yours,

Yuri

• ###### 3. Re: Roll up a dimension based on a % of a measure

Hi again,

Playing with the mystery of scrambling Totals

in version 8.1.16 of the wb (Book1.twbx above),

I found that if one simply flip table calc Total

from Auto to Hide -- and then back to Auto --

everything went OK with Grand Totals on the view :-)

Digging deeper, I found that one simple property change

make this happen, namely:

<column

caption='% of total sales'

datatype='real'

name='[Calculation_1110219104307506]'

role='measure'

type='quantitative'

visual-totals='None'>

Voila, with this bold piece of code

added to the field definition in XML code of Book1.twb

Totals went to the expected behaviour on the view.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Roll up a dimension based on a % of a measure

Yuri - Thank you very much!! I have been struggling with this for a while and this was a good solution.

Something for the Tableau team to think about making easier... Seems like a pretty standard requirement.

• ###### 5. Re: Roll up a dimension based on a % of a measure

wow, this really does appear to be way more complicated than it should be.