Forum Discussion
Multi Variable N:N Relationships?
Hi bkirkey,
Create a table with distinct type value from those tables, then create relationship with 'both' cross filter direction.
Link Table = DISTINCT(UNION(VALUES(Sheet2[Type]),VALUES(Sheet3[ Type])))
Reuslt:
Regards,
Xiaoxin Sheng
Very difficult to picture this with just words, can you post some sample data?
Sorry, here's the part of the data I am talking about with the extra stuff removed. This is what comes in for expenses:
Actual | Region | Type | Quarter |
225 | EMEA | Recruiting | Q1 |
1106 | Asia | T&E | Q2 |
322 | EMEA | T&E | Q3 |
2939 | EMEA | Computers | Q4 |
1596 | Management | T&E | Q1 |
2872 | EMEA | Morale | Q2 |
4656 | EMEA | T&E | Q3 |
1977 | EMEA | T&E | Q4 |
1885 | Americas | Employee Dev | Q1 |
1738 | EMEA | Auto | Q2 |
1772 | EMEA | Dues & Subscriptions | Q3 |
And this is the budget, arranged by Type, Amount, Region, and Quarter.
| r | |||
| Auto | 127849 | Americas | Q1 |
| Computers | 20142 | Americas | Q1 |
| Employee Dev | 76731 | Americas | Q1 |
| Morale | 133345 | Americas | Q1 |
| Recruiting | 83896 | Americas | Q1 |
| T&E | 51930 | Americas | Q1 |
| Dues & Subscriptions | 138190 | Americas | Q1 |
| Auto | 132806 | Asia | Q2 |
| Computers | 33870 | Asia | Q2 |
| Employee Dev | 12697 | Asia | Q2 |
| Morale | 44866 | Asia | Q2 |
| Recruiting | 101465 | Asia | Q2 |
| T&E | 55889 | Asia | Q2 |
| Dues & Subscriptions | 140635 | Asia | Q2 |
I need a chart comparing those side by side on a per region basis.
So it would have the $$ on the Y axis and each region as an X axis, with the legend being each of the "Types." I also need the ability to filter the data by Quarter, so Q1, 2, 3, 4.
Note that for budgets Asia would also have a Q1 like the americas but I hit a character limit :). So you'd have an America, Asia, EMEA, and Mgmt for all four quarters with a seperate type budget for each quarter.
For the expenses there will be thousands of them coming in.
Thanks!
Hi bkirkey,
Create a table with distinct type value from those tables, then create relationship with 'both' cross filter direction.
Link Table = DISTINCT(UNION(VALUES(Sheet2[Type]),VALUES(Sheet3[ Type])))
Reuslt:
Regards,
Xiaoxin Sheng