13 Replies Latest reply on Feb 23, 2018 12:23 AM by Jason Ing

# How to get subset and total counts and use them in a calculation field?

Hi,

Newbie here. I've been searching the forums for answers with no luck.

My goal is to get subset and total counts and then calculate a percentage.

A basic example: There is a "fruit basket" field of 4 apples, 2 oranges, and 3 bananas.

I want just the apple count, total fruit count, and the percentage of apples: a) 4 apples; b) 9 fruits; and c) apples make up.44% of the fruit basket.

I can get a sub count using filters or converting to a measure.

But when I try to create a calculation field using my sub-count and total count fields, Tableau can't calculate it because it says it's a boolean and not a number. I've tried experimenting with sets, groups, parameters, filters, and conversions.

So how do I get sub-count and total count fields that are numbers that can be used in a simple calculation field?

I hope that made sense and I'm using the correct terminology.

Thank you,

Jason

• ###### 1. Re: How to get subset and total counts and use them in a calculation field?

Hi, Jason

What's your data looks like? can you provide a sample data set?

ZZ

• ###### 2. Re: How to get subset and total counts and use them in a calculation field?

I don't have access to my real data set at the moment, so I made one up that captures the essence of it.

It has a column of names and a column of types.

I would like sub counts per name and the percentage of each type.

My goal is to get a real number for each sub total and totals so I can use them in a calculation field.

Does that help?

• ###### 3. Re: How to get subset and total counts and use them in a calculation field?

Hi Jason,

Find my approach stored in the attached workbook v10.3.

Let me know if this helps.

~Tushar

• ###### 4. Re: How to get subset and total counts and use them in a calculation field?

Thank you, Tushar.  But now I understand why Zhouyi asked for a data set. I thought my "fruity" example would be enough and apparently it's was a big mistake. Your solution works but it's based on the quantity and item being in separate columns, correct?  My example would be more like:

1) Apple

2) Banana

3) Apple

4) Orange

5) Apple

6) Orange

So that would be 3 apples, 2 oranges, and 1 banana.

The count is from a string field (column), not a number field.  The excel sheet I attached is closer to my data set.  Sorry for my mistake. But thank you for trying to help me out!

• ###### 6. Re: How to get subset and total counts and use them in a calculation field?

I attached a csv version and also a lower version of Excel just in case there was a version issue. Hopefully one of them should work.

• ###### 7. Re: How to get subset and total counts and use them in a calculation field?

Hi, Jason

Please find my solution attached, as well as screenshot below

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 8. Re: How to get subset and total counts and use them in a calculation field?

Hi Jason,

In addition to directions provided by ZZ find my approach stored in the workbook v10.5

~Tushar

1 of 1 people found this helpful
• ###### 9. Re: How to get subset and total counts and use them in a calculation field?

Thank you, both of you! I can see that both are true solutions to what I'm needing. The different approaches are interesting and I'm going to need a little time to figure out what is optimum for me. But it's awesome to not only have a real answer but two! Thank you, again, and I'll come back to say which one worked best in my case.

Jason

• ###### 10. Re: How to get subset and total counts and use them in a calculation field?

So it looks like I can mark only one answer as correct. I was going to mark both because they were both solutions. I chose ZZ's solution because the {fixed:sum} method (not sure what to call it) proved to be a bit more versatile and independent of other elements.

Thank you again ZZ and Tushar for your help.

• ###### 11. Re: How to get subset and total counts and use them in a calculation field?

You are welcome, and glad to help

ZZ

• ###### 12. Re: How to get subset and total counts and use them in a calculation field?

Hey Jason,

You are welcome. Although you cannot mark two answers correct but you can mark the response as helpful.

Regards,

Tushar

1 of 1 people found this helpful