rpmitchell
New Member
- Joined
- Jun 22, 2011
- Messages
- 43
Hello,
I have a question on how to construct a Formula. I have 4 tables. Each table has an Account number and an Amount. Table 1 is my "Data" table, and can have the same account number multiple times. The other 3 tables are "Lookup" tables, and will not have duplicate account numbers. I have created a relationship between my Data and all 3 lookup tables, using the account number. An account number may or may not be in each of the 4 tables.
I want several different scenarios.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 but not in Table 2 or 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2, but not in 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2 and 3.
I've tried various combinations of Calculate and Filter, but can't seem to get the pattern right. Can anyone give me some insight?
Thanks!
I have a question on how to construct a Formula. I have 4 tables. Each table has an Account number and an Amount. Table 1 is my "Data" table, and can have the same account number multiple times. The other 3 tables are "Lookup" tables, and will not have duplicate account numbers. I have created a relationship between my Data and all 3 lookup tables, using the account number. An account number may or may not be in each of the 4 tables.
I want several different scenarios.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 but not in Table 2 or 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2, but not in 3.
I want to Sum the Amount in Table 4 only if it has a corresponding account number in Table 1 and 2 and 3.
I've tried various combinations of Calculate and Filter, but can't seem to get the pattern right. Can anyone give me some insight?
Thanks!