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!

      Jake

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

          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

           

          1.png

           

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

           

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

           

          2.png

           

          Expected Result:

          3.png

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

             

            Yours,

            Yuri

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

               

              <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
                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
                  james.diaz

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