10 Replies Latest reply on Aug 20, 2013 1:14 PM by walter edgar

# Calculated Measure using two data tables getting errors

This is a theoretical description because of the propreitary nature of what I'm actually trying to do.

Suppose you had a table that had sales transactions by store (multiple rows per store).  Then you had another table that had information on each store, such as location, region, and size of the store in square feet (1 row per store).  I want to know my sales per sq foot of each store, but I also want to know sales per sq foot in each location and region.

When I join the tables (using left or right) the new table puts the store square footage on each row of sales data.  So if I try to calculate sales per sq foot, the calculation uses the sum of the sq feet for each store, giving me the wrong answer.  I can force it to work at the store level by using sum of sales divided by average sq foot.  But obviously that doesn't work at the regional level.

I also tried adding the tables without joining them.  In this case, Tableau determined the link between "store" on both tables and added a paperclip next to store in the Dimensions list.  I can get the calculation to work at the store level by using sum(sales)/sum(Sq Ft).  But I get an error if I try to view the table by region, instead of by store:

And if I try to add date filters from the Sales tabls, the data disappears.

Should I be joining the two tables?  If so, what is the best way to join the tables?  What is the best way to perform this calculation to get ?

• ###### 1. Re: Calculated Measure using two data tables getting errors

Hi Walter,

Beyond using some SQL hacks, the way to work with the join or blend can involve table calculations to deal with aggregating at different levels. In the join case, it's a situation where the joined data has more level of detail than strictly necessary and we can use table calculations to effectively aggregate results for the computation, in the blend case then the level of detail probably needs to be increased in the view (i.e. keeping the store on the level of detail shelf) and then we use table calculations to aggregate the results.

One thing I'm not clear on is, at the region level, what do you want to be showing? In other words, how do you want to aggregate the individual store results - do you want to average an average, or something else?

If you mocked up some data that represents your situation (you could use coffee chain or superstore sales as a basis), I'm sure something could be put together that will meet your needs.

Jonathan

• ###### 2. Re: Calculated Measure using two data tables getting errors

Jonathan, thanks for replying quickly.  I have a Excel spreadsheet with mocked up data and I used PowerPivot to do the calculations I wanted.  I also did a mock up in Tableau.  I don't see a way to upload it.  What's the best way to files to you?

To answer your question, at the region level, I want to sum the total sales for the region, and divide it by the total sq ft for that region.  Like this:

• ###### 3. Re: Calculated Measure using two data tables getting errors

Great! To attach file(s), click the "Use advanced editor" link in the upper right of the editor window, then when that comes up click on the Attach button in the lower right of the editor window.

• ###### 4. Re: Re: Calculated Measure using two data tables getting errors

See sample file attached.

• ###### 5. Re: Re: Re: Calculated Measure using two data tables getting errors

Hi,

See the attached for two options.

For the first option, I set up a data blend. Because the reporting is at the Region level (which is in the Store data source), I used that as the primary, blending on Store. The one tricky bit is that to get the sort right for the chart, I needed to use a calculated field that returns the proper alphanumeric sort because we can't sort a dimension by a measure that is derived from a secondary data source.

In the second option, I used a multiple tables connection with Store as the basis for the data (since there are stores that have no sales). To get the calculations to work for the crosstab, Store needs to be in the view so the SqFt per Store can be properly aggregated, then we can use table calculations to sum up across stores. The chart is much simpler, as all the data is in the same data source and we can use Tableau's built-in sort to get the chart sorted.

Let me know if this works for you, or if you need any other explanation,

Jonathan

1 of 1 people found this helpful
• ###### 6. Re: Calculated Measure using two data tables getting errors

Jonathan,

Thanks for working on this but I can’t open it because I’m using Tableau 7.  Is there a way to save it for Tableau 7?

• ###### 7. Re: Calculated Measure using two data tables getting errors

I'll have a chance tomorrow to rebuild it in 7 (there's no down conversion).

Jonathan

• ###### 8. Re: Calculated Measure using two data tables getting errors

I'll have a chance tomorrow to rebuild it in 7 (there's no down conversion).

That sounds like a good one for the Ideas section. I'd vote for it.

--Shawn

• ###### 9. Re: Re: Calculated Measure using two data tables getting errors

Here's the version 7 version. The crosstab and chart built with using a multiple tables connection are unchanged from the v8 workbook. The crosstab blend for v7 is quite a bit different from the blend for v8, and in fact looks very much like the multiple tables crosstab. This is because in v7 the linking dimension(s) (Store in this case) have to be in the view, whereas in v8 we can blend without requiring the linking dimensions to be in the view.

Jonathan

• ###### 10. Re: Re: Calculated Measure using two data tables getting errors

Jonathan,

thank you very much!  It works great!  I would never have figured out the table calculations without you.  The 2nd option (data blend) works better for me because I can Quick Filter on any dimension, regardless of the source table.

There were two keys that I think others may benefit from:

1. Since "Store" is the linking Dimension, it has to be on the view.  Placing it in the "Level of Detail" box solves that problem.  (I would never have known that.)
2. Once you create the Calculated Fields, you have to select "Compute Using..." and select "Store."  Otherwise you get overlapping text.

And also thanks for converting to version 7.  I hope I get to install v8 really soon.