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

# Calculation Help

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

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

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

try using MAX instead of SUM in the calculated field.

• ###### 4. Re: Calculation Help

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

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!

[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

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