8 Replies Latest reply on Dec 22, 2016 2:53 PM by Justin Pulley

    Create Calculated field to SUM only certain rows

    Justin Pulley

      I have a table that has differences region to region as far as how they break down financial items.  in REG1 they write 1 line item to cover the entire cost and it comes across as BudgetID, Amount; in REG2, REG3, REG4, and REG5 they write multiple line items to cover each cost associated with every that REG1 combines into 1 line item.

       

      So BudgetID is one consistent number and so is ProjectID for each line item.  Amount varies.

       

      What I need to do is SUM IF REG is 2, 3, 4, or 5 AND the BudgetID is identical.  I tried to do this in my SQL query, but it was making the REG1 data unusable.  Can Tableau do this?

       

      Example data

       

      REG     BudgetID    Amount       Line Item

      1           X99            300000       Candy

      1           X99            200000       Candy Bucket

      2           X100          100000       Candy 1

      2           X100          100000       Candy 2

      2           X100          100000       Candy 3

      2           X100          100000       Candy Bucket Handle

      2           X100          100000       Candy Bucket Container

       

       

      As the real data is confidential I cannot attach it.

        • 1. Re: Create Calculated field to SUM only certain rows
          Wesley Magee

          Justin,

          Let me know if I missed something in what you were looking for, but my best guess is that you're looking to combine Line Items based on your IF/THEN statement but leave the other line items as they are. Is that correct? The table would look something like this:

          and it would leverage the following calculation, which either shows the Line Item or "Combined".

          IF [REG] = 2 or [REG] = 3 or [REG] = 4

          AND [Budget ID] = [Budget ID]

          THEN "Combined"

          ELSE [Line Item]

          END

           

          Let me know if you have further questions.

          -Wesley

          • 2. Re: Create Calculated field to SUM only certain rows
            Justin Pulley

            This is telling me that it returns a string, so how do I use the resultant column of Amount from this?  I should have also said I need to then take multiple values from the amount column and then average them for a region

            • 3. Re: Create Calculated field to SUM only certain rows
              Wesley Magee

              Ok, I think we can accomplish what you're looking for, but I should probably clarify first. My calculation allowed you to group things differently, but it doesn't sound like that's what you want to do.

               

              1. What level of detail do you want to show in the view? In your example data, do you want to include all of the dimensions or just one or two (i.e. only show Budget ID or Reg)? This will dictate what level your data is summed at.
              2. How do you identify what values you will average and what you will sum?
              3. Instead of changing the grouping, are you looking to just change the way a row is calculated (sum or avg)?

               

              Thanks,

              -Wesley

              • 4. Re: Create Calculated field to SUM only certain rows
                Justin Pulley

                1. I have Region and a Phase break out (1....n) as rows (EG. REG1 P01) with the average Amount spent

                 

                REG    Phase     Amount

                1          1            150000

                1          2            100000

                2          1            225000 (these a dozens of smaller line items that need summing)

                2          2            175000 (these a dozens of smaller line items that need summing)

                1          1            170000

                1          2            125000

                2          1            215000 (these a dozens of smaller line items that need summing)

                2          2            185000 (these a dozens of smaller line items that need summing)

                 


                The visual representation of this is

                 

                Row label REG 1      Phase 1      BAR Graph of AVG(Amount)

                Row label REG 1      Phase 2      BAR Graph of AVG(Amount)

                Row label REG 2      Phase 1      BAR Graph of AVG(Amount)

                Row label REG 2      Phase 2      BAR Graph of AVG(Amount)

                 

                So the issue is the data that I have access to, like I was trying to say before, is that Region 1 has already added up their SUM for the Region and Phase so it is very easy to get an average on that.  Region 2,3,4,5 have dozens of line items for a single phase that I have to sum up before I can average them per phase for that region.  So I have to do two calculations on Region 2,3,4,5 : Sum all the line items with the matching ProjectID and then I have to use that calculated SUM in a formula that does an average by phase.

                 

                I probably didn't lay this out as well at first and thought Candy would be the best way to show it.   hah.

                • 5. Re: Create Calculated field to SUM only certain rows
                  Justin Pulley

                  I even tried

                   

                  IF [ProjectID] = [ProjectID] THEN

                      CASE [REG]

                          WHEN '2' THEN SUM([Amount])

                          WHEN '3' THEN SUM([Amount])

                          WHEN '4' THEN SUM([Amount])

                          WHEN '5' THEN SUM([Amount])

                      ELSE [Amount]

                      END

                  END

                  • 6. Re: Create Calculated field to SUM only certain rows
                    Wesley Magee

                    Justin,

                    I think you're getting close. Give this LOD calculation a try. It will replace your amount filed

                     

                    IF REG = 2 or REG = 3 or REG = 4 or REG = 5

                    AND [BudgetID] = [BudgetID]

                    THEN {Fixed : SUM([Amount])}

                    Else [Amount]

                    END

                     

                    When you actually use it, put it as an average to get what you're looking for.

                    -Wesley

                    • 7. Re: Create Calculated field to SUM only certain rows
                      Justin Pulley

                      That is close, but it is Summing everything if it's Region 2, 3, 4, or 5 or something strange; I am getting huge numbers.

                      • 8. Re: Create Calculated field to SUM only certain rows
                        Justin Pulley

                        IF [REG] = '1' or [REG] = '2' or [REG] = '3' or [REG] = '4'

                            THEN {Fixed [BudgetID] : SUM([Amount])}

                        Else [Amount]

                        END

                         

                        This seems to have done the trick.  Thanks for prodding me in the right direction.