9 Replies Latest reply on Mar 27, 2017 9:23 AM by Christine Gibson

    Insert a Calculated Row?

    Christine Gibson

      Hello all,

       

      I need some help figuring out a workaround to inserting a calculated row.  On the attached file, I have a set of data where I need to back into an "other" calculation.

       

      for example, you can see the a category, "dressing" has a total of 3,175 dollars.  However, the detail underneath only totals 2,976.

      That is due to the level at which the data can be run, can't change it. 

       

      So I need to find way to show "other" or "unknown" of 199 (3,175 category - 2,976 named products).  I have multiple categories I need to

      do this with.  Any suggestions? I'm in 10.2

       

        • 1. Re: Insert a Calculated Row?
          Okechukwu Ossai

          What's your data source? There are things Tableau was not designed to do easily. This is one of them.

           

          The best and easiest option will be to make the changes at the data source level before bringing the data into Tableau. If you have a SQL database, you could use a union to add a new Product called "others" in the PRODUCT hierarchy. This will be equivalent to SUM(Product) in the CATEGORY Hierarchy partition minus SUM(Product) in the PRODUCT Hierarchy partition.

           

          Ossai

          • 2. Re: Insert a Calculated Row?
            Sirish Bhatta

            i agree with Ossai. Not as designed. However, I believe you can try writing SQL and add a union to create a new category. Open your excel file with legacy option that would give you three feature to write sql. A quick trick is to convert to sql from the menu . Then add a union and create a new category say other and see if you can create a calculated total may be using switch ( heard that case is not supported )  something like sum of product - sum of category. If this tricky then add a 0  you would have a placeholder where you have to use calculation field in tableu try o replace that 0 with the difference.

            Sql or sql based reporting tools would be easy to do this sort of tasks.

            Sirish

            • 3. Re: Insert a Calculated Row?
              Jeff D

              Hi Christine, I agree that Tableau doesn't make it easy to add rows.  One alternative is to add another sheet that shows the unknown products, for example:

              2017-03-25_183617.png

              • 4. Re: Insert a Calculated Row?
                Christine Gibson

                It's an excel file, and I'm afraid my sql skills are non existent. It's syndicated data and so is limited at the detail it can be run.  I'm trying to show a table or graph with % of total. So I need to come up with a way to show 100% of the category. 

                • 5. Re: Insert a Calculated Row?
                  Christine Gibson

                  ok, so can I create a single element (chart, graph, table etc) from these 2 different sheets?  I tried tinkering with a level of detail expression but just ended up confusing myself.  This is my first attempt at merging about 20 different reports into 1 file.  Not doing so great I'm afraid. 

                  • 6. Re: Insert a Calculated Row?
                    Yuriy Fal

                    Hi Christine,

                     

                    The problem you're trying to solve

                    is to have "yet another product" (YAP)

                    member in your 'PRODUCT' Dimension.

                     

                    There is indeed such a member in your dataset already.

                    Guess who? It's the [Hierarchy] = 'CATEGORY' :-)

                     

                    Having this dimension member in place

                    one could calculate aggregated Measures

                    using (not so fancy) LOD expression(s).

                     

                    The fine point is to use the MIN() aggregation

                    for this member only, while using SUM() for the rest.

                     

                    Please find the attached.

                    Hope it helps.

                     

                    Yours,

                    Yuri

                    • 7. Re: Insert a Calculated Row?
                      Christine Gibson

                      Thank you Yuriy.  I am so new at Tableau, I am not even sure what terminology to use to ask my questions! 

                      So I was on the right track with LOD expressions.  Was there an attachment?

                      • 8. Re: Insert a Calculated Row?
                        Yuriy Fal

                        Christine, you're welcome.

                         

                        Don't worry too much about terminology,

                        it is "invented" on-the-fly most of the time :-)

                         

                        Yours,

                        Yuri

                         

                        PS My previous post does have the attachment (workbook).

                        To see / download, please go visit this thread with a browser.

                        • 9. Re: Insert a Calculated Row?
                          Christine Gibson

                          Yuri,  Thank you so much.  This is precisely what I was looking for.  This will be a tremendous help as I

                          work to consolidate these reports.  Thank you again and have a wonderful day!

                           

                          Chris