9 Replies Latest reply on Oct 16, 2013 11:52 AM by Ben Neville

# Avg Amount Spend per Day per Employee per Category.

Hello,

I have to calculate and average \$ spend per employee per day for each category.

I would like to calculate that in a measure item in order to use it in a bullet graph, set a goal and see globaly if I am under the goal or above. (for the company and each employees)

Do you have any idea how to proceed, knowing that I have for one day, multiples rows for each category.

14th March     Paul     Meal     \$70

14th March     Paul     Travel    \$150

15th March     John     Meal     \$80

16th March     Greg     Meal     \$50

16th March     Paul     Meal     \$70

16th March     Paul     Car       \$50

Thank you!

• ###### 1. Re: Avg Amount Spend per Day per Employee per Category.

Hi Jean,

Put Employee on Rows and Category on columns.

Put your measure "Spend" on text and change the aggregated function to AVG instead of SUM that is the aggregation function that is defined by default.

Ramon

• ###### 2. Re: Avg Amount Spend per Day per Employee per Category.

Than you very much.

But I would like a formula to have the same result and use the calculated item in order to bring this data into a bullet graph.

http://kb.tableausoftware.com/articles/knowledgebase/bulletgraph

• ###### 3. Re: Avg Amount Spend per Day per Employee per Category.

Were you looking for something similar to this?

• ###### 4. Re: Avg Amount Spend per Day per Employee per Category.

Hi Jean,

I believe you can do that by simply creating a calculate function. based on my understanding with you post. I have created a sample view and attached here. please see and let me know if that is the you wanna represent .

Thanks,

Singam

• ###### 5. Re: Avg Amount Spend per Day per Employee per Category.

Thank you guys.

That's pretty good for both, even if I would go for Ben visualization but on top of that I want to set up a goal which is 75 for each meal expense every day and set up a goal line and see if we are above or under.

So the blue bar would be the average for each person (average meals expenses per day) and the black/grey line would be the \$75 goal.

With also the background color for the 60/80 % of the Average.

This is in order to look at our goals and see if the people are compliant or not and if on a more generic view the company is compliant.

Thank you.

• ###### 6. Re: Re: Avg Amount Spend per Day per Employee per Category.

What you are suggesting seems fairly straightforward. All you need to do is have this data in your dataset. I wrote it in calculations. You could alternatively put it in your data itself, or even parameterize this to have the budget values selectable in the report.

• ###### 7. Re: Avg Amount Spend per Day per Employee per Category.

That looks great!

Thank you for that

Last question is, how to make sure that your average is based on the number of days.

Here if I am not wrong, this is the average of the amount based on the different people.

But I would like to have the average amount based on the number of days then on the people.

I want to know if Paul had 10 meals on 5 different days, the average per day in order to see if he is compliant to the \$75 goal.

Thank you.

• ###### 8. Re: Avg Amount Spend per Day per Employee per Category.

Ideally it would be something like that:

Avg (Amount, Days) for each [Employee ID]

• ###### 9. Re: Re: Avg Amount Spend per Day per Employee per Category.

To accomplish this you need to perform a COUNTD of the days (because of limitations in Microsoft's JET Engine, to perform a COUNTD in Excel, you need to extract the data as I have done here). You then have the number per day, and all you need to do is divide the sum of all of the expenses by this. Thus you get a per-day average. See the attached solution to see if that meets your needs.