10 Replies Latest reply on Jan 2, 2018 10:55 AM by Cristian Maga

    Add a calculated item - similar to Excel PivotTables

    Stuart Dunlap

      Hi,

       

      I have a cross tab report that summarizes sales for three product categories, Furniture, Office Supplies and Technology.  The report looks like:

       

      Furniture               $1,000

      Office Supplies     $2,000

      Technology          $3,000

       

      I would like to create a calculated item as you would in an Excel PivotTable.  The calculated item would create a new product category called 'Non Tech' that would be Furniture plus Office Supplies.  The resulting report would be:

       

      Furniture                    $1,000

      Office Supplies          $2,000

      Technology                $3,000

      Non Tech                    $3,000

       

      I'm looking for a result that's different than a calculated field.  The problem with using a calculated field is the 'Non Tech' total shows up for every product category, as opposed to creating a new product category.

       

      I don't want to use grouping because I still want to see the individual values.  I also don't want to handle this via SQL using a UNION that appends the 'Non Tech' records to my dataset.  And since I would like this to display in one worksheet, I don't want to use a dashboard solution that combines a report from two different worksheets.

       

      I saw that this question had been asked before in the forum, however it was unanswered...  I'm hoping second time around is the charm.

       

      Any insight is appreciated.

       

      Thanks,

      Stu

        • 1. Re: Add a calculated item - similar to Excel PivotTables
          Mark Holtz

          Hi Stu,

           

          By my estimation, you are essentially asking if you can create a new element of data, which is not really Tableau's jam. Would you be able to duplicate the data source and group the two into your "Non-Tech" then blend the 2 sources together to represent your "new" combination?

           

          Honestly, I find that manipulating the data before bringing it into Tableau is much more straightforward and easier to understand. Is there a particular reason you're averse to pursuing your solution that way?

          • 2. Re: Add a calculated item - similar to Excel PivotTables
            Kishore Kumar Suthar

            Make the duplicate copy of that field and then use grouping

            • 3. Re: Add a calculated item - similar to Excel PivotTables
              Stuart Dunlap

              Mark,

               

              Thank you for your reply.  I'm not adverse to manipulating the data prior to bringing it into Tableau and it looks like that will be the approach I go with.  I just wanted to make sure I wasn't missing out on Tableau functionality.

               

              It helps to hear your advice on manipulating data prior to bringing it into Tableau.  I'm new to this software and with my limited experience, that approach seems easier.

               

              Thanks again for your help.

               

              Stu

              • 4. Re: Add a calculated item - similar to Excel PivotTables
                Stuart Dunlap

                Kishore,

                 

                I want to show the original field values with the newly created category appended to the bottom of my report.  As with my example in the original post:

                Furniture                    $1,000

                Office Supplies          $2,000

                Technology                $3,000

                Non Tech                    $3,000

                 

                Non Tech is just the addition of Furniture and Office Supplies.  If I create a copy of the field and group, I will have to show my original category field and my copied category field side by side. I'm looking for functionality similar to Calculated Items in Excel PivotTables - if that helps.

                 

                Thanks for your reply.

                 

                Stu

                • 5. Re: Re: Add a calculated item - similar to Excel PivotTables
                  Kishore Kumar Suthar

                  Hi Stuart Dunlap

                  I know your problem.It can be easily done in Tableau.There are some Tricks,So follow my step

                  1.Right click on category and make a calculated field named as Non Tech

                  2.Make a another calculated field and write this expression--

                  WINDOW_SUM(sum(Sales),first(),last()-1 )

                  3.Put the Non Tech calculated field into filter and select the product like

                  Furniture               

                  Office Supplies       

                  Technology

                  and into the Rows

                  5.Put their corresponding sales

                  6.Put the second calculated field into column and right click on them and select discrete..

                  You got your output....

                  • 6. Re: Re: Add a calculated item - similar to Excel PivotTables
                    Stuart Dunlap


                    Kishore - I appreciate your follow up.

                     

                    To confirm, for your Step 1, what is the formula for the Calculated Field that I'm calling 'Non Tech'?

                     

                    Thank you,

                    Stu

                    • 7. Re: Add a calculated item - similar to Excel PivotTables
                      Kishore Kumar Suthar

                      No formula in that calculated field,only category field should be there.
                      I attached a workbook there,please see that workbook and work according to that..

                       

                       

                      Kishore Kumar Suthar

                      • 8. Re: Add a calculated item - similar to Excel PivotTables
                        Stuart Dunlap

                        Kishore,

                         

                        Thank you for looking into this and offering an option.  Unfortunately, this method will not work for me.  The WINDOW_SUM will have different offsets depending on which entity is displayed in the report.  (For the real data I'm working with, some entities do not have all categories.)  In addition, I would like more control over where the Non Tech group is positioned in the report.

                         

                        I am new to Tableau so it may be that I'm missing how this is done.  Your workbook was very helpful in furthering my understanding of your approach.

                         

                        Thanks again.

                         

                        Stu

                        • 9. Re: Add a calculated item - similar to Excel PivotTables
                          Kishore Kumar Suthar

                          How it is possible.

                          If you really not get your proper solution then you provide me your workbook.I will solve that problem and send you.

                           

                           

                          Kishore Kumar Suthar

                          • 10. Re: Add a calculated item - similar to Excel PivotTables
                            Cristian Maga

                            As already stated, there is no straightforward equivalent of "Item" calculation in Tableau. So within a WORKSHEET there is NO WAY to list several metrics (rows) for dimensions at the top AND to ADD another column with derived ("ITEM") calculation.

                             

                            HOWEVER ... if your goal is to display results in a DASHBOARD it is really easy to display the "ITEM" as a separate column:

                            1) Duplicate the sheet with regular dimension(s)

                            2) Use the new sheet to compute ONLY the derived calculation (table calculation would be simplest if suitable)

                            3) Show the two worksheets next to each other in the dashboard

                            • un-select "Show Header" for the Measure Names in the derived worksheet
                            • make sure that filters are applied to both worksheets

                             

                            I used this approach to show in "regular" worksheet the 2017 and 2016 values for a (long) list of metrics. Then the YOY calculation (2017 "percent difference from" "Previous/Next") is done on the duplicated version of the "regular".

                            ... True ... this may backfire if changes are being made in only one of the two worksheets.