3 Replies Latest reply on Jan 9, 2019 5:02 PM by Ken Flerlage

# Calculation/Count help

Hello!

I'm a pretty basic Tableau user, and am trying to figure this out:

I want to count only Category>Gym membership when it is the only item on an expense report (i.e. Report ID is unique) like report 24385810, or it is the only category on an expense report like 24385676, but not when there are other categories present. (If these have to be two different calculations, that's fine.)
Context: We are trying to figure out how much time/money spending on expense reports where employees are only submitting their gym membership.

Basic sample below, larger sample attached.

 Category Report ID Report Currency Report Total
 Gym Membership 24385676 USD 42.79 Gym Membership 24385776 USD 32.09 Gym Membership 24385810 USD 32.09 Transportation- Parking 24392421 USD 335 Transportation- Parking 24392421 USD 335 Transportation- Parking 24392421 USD 335 Equipment (not related to computer) 24392421 USD 335 Gym Membership 24392421 USD 335 Professional Development/Continuing Education 24392948 USD 11.18 Gym Membership 24393641 USD 35 Professional Development/Continuing Education 24396200 USD 31.91 Gym Membership 24399360 USD 50

-Jennie

• ###### 1. Re: Calculation/Count help

You can start by creating a calculated field that will give you the number of categories on each expense report. I'm going to use fixed LODs (for more about this, see FIXED Level of Detail Expressions - Tableau).

Category Count

// Count the number of categories on the expense report.

{FIXED [Report ID]: COUNTD([Category])}

Then you can filter for anything with Category Count = 1 and filter on Category = Gym Membership. This will give you a list of all expense reports with only gym membership on them.

See attached.

2 of 2 people found this helpful
• ###### 2. Re: Calculation/Count help

THANK YOU for your speedy response! We processed over 1200 reports containing only this benefit (we are looking to move it to a straight payroll deduction for efficiency/cost savings). Much appreciated!!

• ###### 3. Re: Calculation/Count help

Yes. Love to see this kind of data informed decision making! Glad I could be part of it.