6 Replies Latest reply on Sep 17, 2012 7:47 AM by Russell Christopher

Calculate average of sum

I have a deailed level data table.  Say store sales by day (Fields: Region, store, date, sales).  I need to get store monthly total, whcih I can use sum.  However, I need region average store sales:

Region     Month 1 Month 2 ...

A               100          140

B               120          150

C               110          90

How should I do his?

• 1. Re: Calculate average of sum

The best thing for you to do is reshape your data so it looks more like this:

 Region Store Date Sales 1 1 1/1/2012 12 1 2 2/1/2012 23 1 3 3/1/2012 42 2 4 4/1/2012 12 2 5 5/1/2012 23 2 6 6/1/2012 44 3 7 7/1/2012 11 3 8 8/1/2012 23 3 9 9/1/2012 34

The way you have the months running across the page is going to cause you all kinds of trouble. Tableau posted an unsupported Excel add-in the works great. Give it a try.

--Shawn

• 2. Re: Calculate average of sum

Thanks Shawn.  I did reshape the data.  In tableau, what's the next step?  I want to show average total region sales.  I only can apply one calculation for the "Sales" measure.

 Region Store Date Sales 1 1 1/1/2012 12 1 2 1/1/2012 23 1 3 1/1/2012 42 2 4 1/1/2012 12 2 5 1/1/2012 23 2 6 1/1/2012 44 3 7 1/1/2012 11 3 8 1/1/2012 23 3 9 1/1/2012 34

Result:

Month     Avg Regin sales

1/1/2012          74.6               (77+79+68)/3

Thanks!

• 3. Re: Calculate average of sum

Xiaodong,

Here's one way of doing it. At this point there are lots of different ways of manipulating your data. By the way, the way I did doesn't average an average like you were doing, so there isn't any rounding error.

--Shawn

• 4. Re: Calculate average of sum

Thank you so much Shawn.  This is very helpful.

I have another question, might not be related to Calculation.

I have one data source with 20 million records at store level.  The 2nd data source is small, which has a region name for each store.  I need to link them to get region assignment for the Big data source.  Edit relationship doesn't help me.  Since it alway need to bring in the "store" in the calculation, as I try to gat a region average for calculation.  Am I doing something wrong or there is another trick?

I can build the BIG data with region as a column, but that will take too much space...

Thanks

• 5. Re: Calculate average of sum

Xiaodong,

I don't work with big data, so I'm not going to be much help. You should probably post this as a separate question, because once a question is marked answered, then the Guru's usually just skip it. Russell Christopher is online at the moment, and if this ping gets his attention, he's the guy that can help you with this sort of question.

--Shawn

• 6. Re: Calculate average of sum

Consider using Data Blending "Primary Groups" which allow you to join data at one level (store) , but then group the data at a different level (region):

http://kb.tableausoftware.com/articles/knowledgebase/how-create-primary-groups-secondary-source

I've attached a sample for you.