3 Replies Latest reply on May 10, 2018 7:11 AM by Zhouyi Zhang

# Calculating the amount per day on a chart

Hey community!

I am a relative newbie to Tableau, and am trying to solve something that I think should be straightforward, but I just cannot figure out how to do it.

I have a Tableau chart that shows on a timeline the exports of certain commodities.  This data is made up by summing all the individual movements of that commodity (on ships, mainly).

So, in my underlying data I might have:

Vessel 01     01 Jan 2018     100 Barrels               Country 01

Vessel 02     01 Jan 2018      150 Barrels               Country 02

Vessel 03     02 Jan 2018     99 Barrels...etc          Country 0`

So, this is currently represented like this on the output:

Which is great!  Except....

For crude, the general metric used isn't the total number of barrels produced, it's the barrels per day.  So, if a given country exported 100 barrels on day 1 on a ship, and 100 barrels on day 1 on a different ship then this would be 200 barrels per day.

This is fine if I zoom into the above graph so, instead of it showing months, it shows days.  This would simply sum the exports per day - hence barrels per day.  The pill for they y-axis is simply SUM([Barrels Exported]). Or:

But, if I zoom out to show weeks, months, quarters, or even years, then the thing falls down.  If I look at months, for example, then the chart sums all of the barrels exported in that month but I want it to give me the average amount of barrels exported per day in that month.

So, to make the maths simple, let's look at a week.  Let's assume that there are 7 vessels departing in that week and each of them had 100 barrels on.  In my current graph, this would come up as 700 Barrels for that week - but the number I need is the barrels per day, which, in this case, would be 100 barrels per day for that week ((7 * 100) / 7).

Of course, it gets even trickier when looking at months because, of course, the number of days in a month is not consistent.

So, the dream is to still allow the users to zoom in and out of the timeline (change months to weeks...etc.) and not touch that functionality - but to be able to have the system generate the barrels per day regardless of the zoom level selected by the user on the x axis.

Err.... help!

Tom.

• ###### 1. Re: Calculating the amount per day on a chart

Hi, Thomas

Please find my solution attached with super store data.

Hope this helps

ZZ

• ###### 2. Re: Calculating the amount per day on a chart

This is perfect!  Does exactly what I had hoped.

Thanks!

• ###### 3. Re: Calculating the amount per day on a chart

No worries, I am glad to help

ZZ