Forum Discussion
establishing multiple table relationships
Hello,
I have four tables with approx 1 million rows. Each table includes data from Jan 2018 until Present. Table 1) contains total count of calls Made at the Employee level(3,000 employees). Table 2) contains the aggregate count of calls made by all employees at the company level(6 companies). Table 3 contains amount of credits given by employees. and table 4 contains the amount of fees waived by employees. I created a bridge table based on company and I then attempted to create a new measure to calculate credits given as a percentage of calls made at the company level and employee level. I am also trying to calculate fees waived as a percentage of calls made at the company level and employee level .when I view this new measure the calculation is way off, it is extremely low, My end goal is to include the Monthly Fee Waiver rates and credit rates in a chart that allows me to drill down to site level and agent level. Can anyone help me? Should I be using a different method to establish a relationship?
| Employee Calls made | |||
| Company | Employee Id | Calls Made | Month |
| E*TRADE | Tom | 728 | January |
| Suntrust | Mike | 2 | February |
| Bank of America | Bobby | 546 | June |
| Merrill | Bill | 5 | February |
| vizant | Joe | 376 | January |
| Coke | Sam | 295 | March |
| Company Calls made | ||
| Company | Calls Made | Month |
| E*TRADE | 2000 | January |
| Suntrust | 1344 | January |
| Bank of America | 3444 | June |
| Merrill | 23443 | February |
| vizant | 22234 | January |
| Coke | 544545 | March |
| Employee Credits Given | |||
| Company | Employee Id | Credits Given | Month |
| E*TRADE | Tom | 121 | January |
| Suntrust | Mike | 121 | February |
| Bank of America | Bobby | 43 | June |
| Merrill | Bill | 5454 | February |
| vizant | Joe | 22 | January |
| Coke | Sam | 295 | March |
| Employee Fees waived | |||
| Company | Employee Id | Fees Waived | Month |
| E*TRADE | Tom | 121 | January |
| Suntrust | Mike | 121 | February |
| Bank of America | Bobby | 43 | June |
| Merrill | Bill | 5454 | February |
| vizant | Joe | 22 | January |
| Coke | Sam | 295 | March |
3 Replies
Perhaps if you provided your calculation that would assist. Also, I wonder if you couldn't just merge/join all of those tables together perhaps? With the data as provided, you would need a bridge table for Employee perhaps as well as Company and then use USERELATIONSHIP in your calculation perhaps.
FeesWaived divided by Calls made =
DIVIDE(
SUM('Employees fee waived[Fees Waived]),
SUM('Employees Call made"[Calls Made])
)^^^that is the quick measure I am using I am also doing the same for credits given. I think its a relationship issue because when I try to combine data from different tables and look at a monthly view of the data all my numbers are the same. for example , I used waived fee data from my 'employees fee waived table' and took the date from my 'employee call made' table and created achart. In this chart each month showed 1,200,000 fees waived . That is definetly not correct. In actuality some months have way more that 1.2 million and some have way less..
Can I do a merge/join if the same employees are not included in all tables? For example some employees may have made calls but they may not have waived fees so they wouldnt be in the 'employee fees waived table'. If using a bridge is the way to go can you give me a little more detail on how to do this? I am still a novice, thanks your help!!
Hi dw700d,
The relationship should based on what you want to calculate.
If it is convenient, could you please share your desired output based on your data sample so that I can understand your requirement better and get the solution.
Best Regards,
Cherry
