I'm building a pivot table that is reporting sales numbers from 2 different tables. These tables have a similar "age" field. This age field from one of the tables (let's call it Table 1) is the dimension of the pivot table. For each value of the age dimension (each row of the pivot table), I want to sum sales data from the other table ( let's call it Table 2). I built a formula in the expression editor to sum the sales data from Table 2, with an age criteria. Am I able to reference the age dimension in this formula? Meaning if the pivot table has ages 60-65 as dimension rows, how do I sum the sales data from Table 2 when the age field from Table 2 equals the value for the age dimension (from Table 1) for that row of the pivot table?
For instance, in the pivot table row age = 60 (From Table 1), I want to sum the Table 2 sales when the Table 2 age also equals 60. The formula would need to be dynamic though such that each row of data would sum based on that row's age value. I tried including the age variables in my sum formula ( Sum( { $ < age2 = {age1} > } sales ) ), but it resulted in 0's in the pivot table.
