12 Replies Latest reply on Oct 28, 2016 7:45 AM by isa.serah Branched from an earlier discussion.

# Show Total Sales and Average Daily Sales by Month/Year on same chart

How to display sales 2015 ,sales 2016 ,Average sales 2015 and average sales 2016 in the same graph.?I could do dual axis for sales 2015 and sales 2016 .How can I achieve the other two?It would be helpful if someone can show that in a workbook.

Ho can I create a chart like the picture below.I need to calculate the daily average sales of 2015 and daily average sales of 2016 and it should be represented as lines shown in the picture and Sales2015 and sales 2016 as 2 bars .But all in same view.Can you please help me in that.

Message was edited by: Simon Runc

• ###### 1. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

hi isa.serah I've branched this off (exercising my Tableau Ambassador powers!) to a new thread. This is a different question, so will make it easier for others to find the solution.

So, as always, there are several ways we could do this. I think the (probably) the simplest is a follows;

I add SUM([Sales]) to the Rows shelf, and Month (of Order Date) on Columns (I've also filtered to last 2 years). I then add Year([Order Date]) to the colour shelf. This means I have a bar per year. However, by default Tableau "stacks" bars, so I need to change the default behaviour from the Analysis Menu (Stack to Off)

I then also bring Year onto the Size Tile, so we get smaller bars for last year.

Next to get the average daily sales. As I'm using Superstore, and the "grain" of the data is Day/Order/Product, I need to use a LoD so I get the SUM for each day, which I can then average (if you're data is at the Day grain, you won't need this step). I use the following calculated field

[Daily Sales - LoD]

{FIXED[Order Date - Day]: SUM([Sales])}

I can then bring this in as a second Axis, and colour on Copy of Year (I've used Copy as you want the Average Daily version to be coloured differently from the year used in the Bars). I can use the line mark, and then Dual-axis the charts.

Hope that helps, and makes sense.

3 of 3 people found this helpful
• ###### 2. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Thanks a lot .

• ###### 3. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Hi Simon,

I have a question.I dont know if its possible in Tableau.But wanted to know .Can we call a particular sheet just like how we call a parameter into the calculated field?Can we do that in tableau?

• ###### 4. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Do you mean like Excel, where you can reference a cell/calculation...etc. from another sheet?

Unfortunately not (although personally, I think it's a good thing!!)...however I might be in the minority as here's the idea (with quite a few votes!) if you want to vote it up https://community.tableau.com/ideas/1604

Each sheet is its own "query" over the whole data, and as such thinks (and works) very differently to Excel.

Here's on of my fave slides from my Tableau training deck, which kind of explains why...btw this is taken directly from a post that Jonathan Drummey wrote (to give the correct credit!)

• ###### 5. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Yes Simon.So I also have voted

• ###### 6. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

to be fair I'm not so opposed to the idea of using it to be a "datasource" (that would give some of the, more advanced, ETL stuff that's missing in Tableau)...but my days of =('Sheet1'A2+'Sheet1'K2)/'Sheet4'X1 are (hopefully!) well and truly behind me!!

...One of the main reasons Tableau can operate in seconds over 100s of millions of rows, and Excel takes 30 seconds to calculate a few hundred thousand rows, is that set-based technologies are far faster...albeit that a bit more thought needs to go in, upfront, on the data shape/structure/grain side, but that's no bad thing!!

• ###### 7. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

My idea was to make change to a sheet based on 3 different sheets.ie the 3 separate sheets are represented as line graphs each of them showing 3 diffrent measures.(no of shipment,no of returns ,no of customers).If I select first sheet showing no of shipment  the 4 th sheet which is represented in bar chart also  shows no of shipment.If I select 2nd line sheet showing no of returns,then the bar chart should give me the no of returns and so.So basically I wanted all the three line graphs to act as action filter.I feel if tableau provide someway of calling this sheets it would be great.but without compromising the query retrival time.

• ###### 8. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Ah, I see...I was very much referring to the idea (which some have requested in the comments to the "data source idea") of setting up a sheet with a load of filters (say last year sales), and then referencing the results (a la Excel VLOOKUP/INDEX-MATCH) in another sheet, which would be filtered to this years sales...this is the type of thing you think you want, until you actually get it

So on your situation, you basically want the functionality of a parameter-driven sheet swap, but the swap is driven by an action, rather than someone selecting a different value in a drop-down parameter? I totally agree, and think using actions to switch sheets is so much more intuitive for the end-user (or myself if it's a model for me to use!!)...and you are in luck! the following wiki post shows how to do this.

Let me know if you have any problems (and probably best in the wiki comments thread, so all that information is in the same place), and I'll be happy to take a look.

1 of 1 people found this helpful
• ###### 9. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

How can I show jan ,Feb,Nov,Dec months as Winter Months and Others as summer months?Can You please help me on that?I dont know how you will see my questions if i post as new question.thats why i am posting my questions as  replyto your previous answers.sorry for that.

Thanks

• ###### 10. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

have figured that out.

• ###### 11. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

cool...yes that one is all about the DATEPART!! - after that, you can use an IF THEN ELSEIF to create your seasons.

It's better to post a new question...as it means others with the same problem can easy find the answer, and likewise in this thread for the question in the title. There are lots of very helpful people here (it is one of the best things about Tableau...it's rich and helpful community!), so it's unlikely your question would go unanswered for long (there is even a crows-nest viz so the community can see any questions that haven't been answered...which we all check often). But you can also ping members (which sends an alert to their inbox), which is when you need a certain person's help/thoughts/advice (especially if you know them to be an expert in that aspect...say Jeff or Toby for server stuff). If you type @  and then start typing someone's name the IntelliSense will bring you up the members matching that string.

1 of 1 people found this helpful
• ###### 12. Re: Show Total Sales and Average Daily Sales by Month/Year on same chart

Thanks.Will do it