9 Replies Latest reply on Oct 3, 2013 9:35 AM by Natalie Woo

    How to implement formula into different groups in tableau?

    Natalie Woo

      hello,

       

      I am working on transfering crystal report to tableau, we set up different layer (groups) within the company.

      E.g. North, South, West regiono then North west, Mid west... and break down into each operation company.

       

      If I want to set up a calulation lets say profit=revenue- cost and I want to apply this formula on the region level, then area level then segment level... how do I set this up?

       

      In crystal re[prt, I set it up with this syntax :

       

      (Sum({revenue},{REGION})- (Sum({cost},{REGION})

       

      How do I do it in tableau? Thank you~

        • 1. Re: How to implement formula into different groups in tableau?
          Shawn Wallwork

          Natalie, it is best if you click the "Forums" option on the Community page and then post your question. Out here on the Community page questions role off the bottom after a couple of days (faster when the spammers attack) and your question will be lost. When you post inside the forums your question remains visible to everyone, for all time. Also, most folks don't look at this page very often.

           

          The short answer is to create a calculation like this:

           

          SUM([Revenue]) - SUM([Cost])

           

          Then put [Region] on the row shelf, next put [Area] on the row shelf to the right of region. And finally put your calculation on the text shelf. The figures will be broken down by region by area.

           

          --Shawn

          • 2. Re: How to implement formula into different groups in tableau?
            Matt Lutton

            In Tableau, you could simply calculate profit once as SUM([Revenue])-SUM([Cost]).  Then, as long as Region, Area, and Segment are dimensions, you can place them in your view, and the profit calculation will display at the level of detail you have placed in the view (based on the dimensions I just mentioned).

             

            Does this make sense?

            • 3. Re: How to implement formula into different groups in tableau?
              Natalie Woo

              Thank you veru much Shawn, Im new to the forums and I will follow your instruction for my next post.

              Thanks again for all your help

               

              What if i have a different formula for each level? How can I create calculations on each group? Thanks

              • 4. Re: How to implement formula into different groups in tableau?
                Natalie Woo

                Thank you Mattew, what if i have a different formula for each level? How can I create calculations on each group? Thanks

                • 5. Re: How to implement formula into different groups in tableau?
                  Matt Lutton

                  If you mean for Region, Area, and Segment, like I said, you don't need to.  Tableau will display the Profit calculation based on the dimensions in your sheet.  As Shawn said, if you place Region and Area on the Rows shelf, then the Profit calculation on the Text shelf, you will be shown Profit at the area level.

                  • 6. Re: Re: How to implement formula into different groups in tableau?
                    Matt Lutton

                    Here is an example, where I've calculated sum(Profit) divided by sum(Sales), and I've placed Market, then State on the Rows shelf.  Tableau automatically displays the calculation at the level of detail I have placed in the view (in this case, broken down by Market and State)

                     

                    This is one of the things that makes Tableau such a powerful tool.

                    • 7. Re: How to implement formula into different groups in tableau?
                      Natalie Woo

                      Thank you Mathew,

                       

                      Im sorry I did not ask my question clearly, if the equation for the region and market area are different:

                      e.g. Region profit= revenue- cost

                            Market Area Profit= revenue-(cost*2)

                       

                      Do i have to create 2 calculation field and apply one for the Region and the other one for the Market Area?

                       

                      The report that I am working on it is little unusual. Usually a group of market area make up a region e.g. Northwest + mideast = North Region

                      Tableau automically calulated the average profit for the Northregion for me based on the values of the Northwest and mideastarea. However, the North Region profit is "not" the average of the Northwest + mideast. It has a separated formulato calculate it. I know this does not make a lot of sense but this is a special report that we need to use a separate formula to do the Region calculation. Therefore I am realy struggling with how to do this.

                       

                      Would you please assists me please? Thank you si much

                      • 8. Re: How to implement formula into different groups in tableau?
                        Matt Lutton

                        You can always write different calcs, so yes, that is an option.  You may be able to write one calc to calculate profit for each, but it would depend on how your data is structured and we know nothing about that.

                         

                        For example, if "Region" and "Market area" were members of the same field, then you could write a calc like:

                        if [FieldName] = "Region" then [Your Region Calc here]

                        elseif [FieldName] = "Market area" then [Your Market area Calc here]

                        etc, etc.

                        END

                         

                        In order to get the best possible help on the forum, packaged workbooks are always your best bet.  If you can scramble your data, or work up a scenario similar to yours using the sample data provided, then we can actually see what you are seeing and try to help you the best we can.  Its difficult to answer complicated questions for a visual tool without seeing the data.  I understand that we can't always post our workbooks, but we can typically replicate the problem in a sample.

                        1 of 1 people found this helpful