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
