8 Replies Latest reply on Oct 12, 2016 8:59 AM by Luciano Vasconcelos

    Drill Down Into Sub Category

    Michael Weber

      Hello All,

       

      I have attached a wkbk for reference, please refer to the dashboard. It uses the superstore dataset.

       

      I only want to display the top two categories by profit, at the same time the quick filter allows me to view any of the three categories.

       

      I want the ability to select a category in the quick filter and have the sub categories for that specific category replace the category view.

       

      So if I filtered on Furniture, I would see "bookcases, chairs, furnishings, and tables" Please note I only want to see the sub categories when a category is selected.

       

      Thanks,

      Michael

        • 1. Re: Drill Down Into Sub Category
          Lisa Li

          Hey Michael,

           

          I'm not sure what you're asking for is possible without the use of a parameter. Below is my solution with a couple of calculated fields and a parameter:

          cate.PNG

          Create a parameter Category Parameter and use it in a calculated field:

          if [Category Parameter]=[Category] then [Profit]

          elseif [Category Parameter]="All" then [Profit] END

           

          Then create a calculated field, called Sub Category Labels in my example:

          if [Category Parameter]="All" then ""

          else [Sub-Category] END

           

          Put both [Category] and [Sub Category Labels] in rows and [Category's Profit] in columns. [Category's Profit] is:

          if [Category Parameter]=[Category] then ([Profit])

          elseif [Category Parameter]="All" then ([Profit]) END

           

          Filter by [Category] again to only show the top 2 categories based off of [Category's Profit] so you don't exclude a selected Category.

          filt cat.PNG

           

          And also filter by [Category's Profit] to only show values that exist (so you don't show other sub-categories when they're not selected or when "All" is selected).

          non null.PNG

          Lastly, here is the view of the sheet when Technology is chosen in Category Parameter:

          Furniture.PNG

           

          Hope this helps!

          -Lisa

          CoEnterprise | Blog

          1 of 1 people found this helpful
          • 2. Re: Drill Down Into Sub Category
            Michael Weber

            Hi Lisa,

             

            This looks great, however I could not get it to work.

             

            Would you mind attaching your workbook?

             

             

            Thanks,

            Michael

            • 3. Re: Drill Down Into Sub Category
              Lisa Li

              Here is the workbook attached. Let me know if you have any questions!

               

              -Lisa

              CoEnterprise | Blog

              1 of 1 people found this helpful
              • 4. Re: Drill Down Into Sub Category
                Michael Weber

                Lisa,

                 

                This is super, I used the superstore set as an example. However, with the actual data set I am working with there is an issue.

                 

                The measure in this case is "profit" but the measure I want to use in my real life data set is a created calculated field and when I try to write a calc field modeled after "Category Profit" I receive the below error that I know a lot of folks have seen one too many times.

                 

                "cannot mix agg and non agg expressions/comparisons using an if statement"

                 

                Is there a workaround? Maybe a different way to write this calculated field modeled after "Category Profit"

                • 5. Re: Drill Down Into Sub Category
                  Luciano Vasconcelos

                  Michael, post this formula please.

                   

                  You can try to use attr function but is hard to say without looking.

                  • 6. Re: Drill Down Into Sub Category
                    Michael Weber

                    Hi Luciano,

                     

                    The data is confidential unfortunately.

                     

                    I just need a workaround, since the formula wont work when I am mixing aggregate and non agg

                    • 7. Re: Drill Down Into Sub Category
                      Lisa Li

                      Hey Michael,

                       

                      Like Luciano said, it's hard to say without looking at the kind of data you're working with. Did you try using the ATTR function? You can try ATTR([Measure]) instead of [Profit]. However, depending on the data, it may or may not work.

                       

                      Otherwise, can you give me more information about the calculated field you're using? For example, a sample crosstab of what the data looks like and what the calculation is doing?

                       

                      -Lisa

                      CoEnterprise | Home

                      • 8. Re: Drill Down Into Sub Category
                        Luciano Vasconcelos

                        We don't need real info, but formats and formulas

                         

                        Enviado do meu iPhone

                         

                        Em 11 de out de 2016, às 21:53, Michael Weber <tableaucommunity@tableau.com> escreveu:

                         

                         

                        Tableau Community

                         

                        Drill Down Into Sub Category

                        resposta de Michael Weber em Forums - Visualize a discussão completa

                         

                        Hi Luciano,

                         

                         

                         

                        The data is confidential unfortunately.

                         

                         

                         

                        I just need a workaround, since the formula wont work when I am mixing aggregate and non agg

                         

                        Responda a esta mensagem respondendo a este e-mail ou vá para a mensagem em Tableau Community

                        Inicie uma nova discussão em Forums por e-mail ou em Tableau Community

                        Seguindo Drill Down Into Sub Category nestes fluxos: Caixa de Entrada

                        Este e-mail foi enviado por Tableau Community porque você é um usuário registrado. Você pode cancelar a assinatura da Tableau Community agora mesmo ou ajustar a frequência de e-mail nas preferências do seu e-mail