I want to perform customer segmentation analysis based on two tables: a list of Customers and a list of Transactions. I want to add a field to my Customer table that shows total revenue for each Customer based on sales associated with that Customer in the Transactions table. A simplified data set is attached.
I do not want the value in this field to change when filters are applied. This is dimensional data that will allow me to apply segmentation flags to each customer based on various logical calculated fields
I may want create additional fields to aggregate revenue for certain periods. For example, "2011 Customer Revenue," "2012 Customer Revenue"
How can I create this additional field in my Customer table? I assume I have to use a calculated field or Custom SQL, but I am new to combining multiple data sets and appreciate your insight.