3 Replies Latest reply on Jan 31, 2019 9:37 AM by Dv P

    Dynamic percent of total

    Dv P

      Hi,

       

      I am trying to create a % of total stacked bar chart for dimension named 'category_sub_category'. I want to know if it is possible to do this without double counting.

       

      The following grid shows what the data looks like. Note that I have Category and Sub-category combined into a single column.

      Category_sub_category
      Units
      Furniture100
      Furniture - Chairs60
      Furniture - Tables40
      Office Supplies30
      Office Supplies - Paper15
      Office Supplies - Labels15

       

      Now I want % of total units for the selected items as shown below. 'Furniture' units is 40 because we want to exclude 'Furniture-chairs' to avoid double counting.

       

      Category_sub_category
      Units% of Total
      Furniture4031%
      Furniture - Chairs6046%
      Office Supplies3023%

       

       

      On my dashboard, I have category_sub_catagory as multi-select filter. When I select Furniture - Chairs,  it should exclude Furniture-chairs = 60 to avoid double counting. However Office supplies shows total units because the sub-categories are excluded.

       

      Can we do this at all in Tableau?

       

      Let me know if my question is not clear.

       

      Thank you in advance!

      Diya

        • 1. Re: Dynamic percent of total
          Zhouyi Zhang

          Hi, Diya

           

          You can create a customized group by right clicking your sub-category and manually group them as your requirement.

           

          ZZ

          • 2. Re: Dynamic percent of total
            ArseneXie

            Hi Diya,

             

            How about using "Set Action"?

             

            As the Attachment.

             

            assume the data  is :

             

            Sheet1 is the grid, and Sheet2 is the filter.

            In Sheet1, the ratio of total calculated by DispName.

            And the DispName worked with set action, (Cate-Sub as a set.)

               - In the Set, ==> Cate-Sub Name

               - Out of the Set ==> Cate Name

             

             

            if choose furniture - Chairs

             

            If choose furniture - Chairs and paper

             

             

            If choose all

            HTH.

             

            Arsene

            • 3. Re: Dynamic percent of total
              Dv P

              Thank you very much, ArseneXie. This works great with limited number of values in category and sub-category.
              In my real dataset, I have 15 categories and about 50 sub-categories. And this can change every month. So I want to avoid maintaining manual set. Is there an alternate way to achieve this?

              Also, is there an alternate approach to set actions? We are currently on 10.5. We will move to 2018.3 but later this year. Would like to add this in my dashboard before we move to 2018.3.

               

              Thanks.