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

    Calculated Measure using two data tables getting errors

    walter edgar

      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 ?