6 Replies Latest reply on Jan 4, 2013 12:23 PM by Alex Kerin

    Calculation Help

    Audrey Wobst

      I have a table that shows A sales and B sales by group.  There are underlying salespeople within each group (not shown on table) that sell A, B, or BOTH.  I have created another field in the table that shows the Percent of Group Selling, which is the number of sales people selling in the group/total sales people in the group (COUNT[Name-Selling]/COUNT[Name- All].  The Name-Selling field includes all salespeople selling either A or B and the Name-All field includes all salespeople in that group, including those with no sales.   

       

      Right now this Percent of Group Selling shows the percentage of people selling either A or B.  I'd like to break it out by Percent of Group Selling A and Percent of Group Selling B.

       

      I'm assuming there must be a function that would let me only use the above calcuation when A>0 or B>0 but am not familiar enough with calculations and functions.

       

      Does anyone know a function I could use to do this?

       

      Thanks

        • 1. Re: Calculation Help
          Shawn Wallwork

          Hi Audrey,

           

          I didn't really follow all you're saying but the short answer to the question about A>0 is to create a formula like this:

           

          IF A>0 THEN COUNT[Name-Selling]/COUNT[Name- All] ELSE (Do Something else) END.

           

          If you post a sample packaged workbook with a little better explanation of what you're looking to accomplish, we'll be able to better help you.

           

          --Shawn

          • 2. Re: Calculation Help
            Audrey Wobst

            Hi Shawn,

             

            Thanks for the reply.  Unfortunately I cannot post the packaged workbook because it is confidential information and will require too much to change. 

             

            I tried your solution and am getting errors involving mixing aggregate and non-aggregate information.  If I do SUM(A)>0 it doesn't get me what I want...

             

            Basically, I want to only count a Name-Selling person if A>0 for that particular person. The SUM function is looking at the total A for the entire group, not by individual.  Is there a function or solution that will let me find all the [Name-Selling]s when A>0?  Then I can count those as you said.

            • 3. Re: Calculation Help
              donna trinh

              try using MAX instead of SUM in the calculated field.

              • 4. Re: Calculation Help
                Alex Kerin

                Audrey, all we need is 10 or so lines of your data, changed if necessary. See here: http://community.tableau.com/thread/121116

                 

                While it may be some work, trying to give you the right answer in the absence of real data is also some work.

                • 5. Re: Calculation Help
                  Shawn Wallwork

                  Audrey, what Alex said. With a minor edit: "... is a LOT of work." Especially when you consider without data or a packaged workbook this is a typical Q&A exchange:

                   

                  A: How's this?

                  Q: No I meant__

                  A: OK, how's this?

                  Q Well that's close, but...

                  A: OK, does this work?

                  Q: Yes, that's mostly it!

                  .... ad nauseum.

                   

                  [Audrey, please don't take this post personally, it is just a conversation that gets posted over and over again on these forums. In fact I think most of us who answer questions, spend more time having this exchange than actually solving problems and answering questions.]

                   

                  --Shawn

                   

                  EDIT: See below!

                   

                  EDIT #2: I just realized that the original question was asked in June 2012! Dang Donna, you're digging deep. I wonder if Audrey is still around...

                  • 6. Re: Calculation Help
                    Alex Kerin

                    Or: "Yes! that works. Now my real data set looks like this...."