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

    Roll up a dimension based on a % of a measure

    Jake White

      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

      2-19-2015 11-02-41 AM.jpg

      • 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!


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



          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
            Yuriy Fal

            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 :-)




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

              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:



              caption='% of total sales'







              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.




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

                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.