March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hey all,
I am a little stumped on a task I've been asked to do. I have two tables that have three identical variables I need to compare against each other: budget vs spend.
The variables in each table that need comparison are type of spend, quarter, and world region. Each quarter every region gets 7 different types of spend to budget for. This means I have 112 different budgets all of which will duplicate one of these fields at least 4 times.
For the expenses I've managed to rig it up so that those same 3 fields will match to the budget table. I can create in the BI report two graphs: one for budget and one for spend. I can't get them to connect because Power BI complains that it can only handle ONE relationship of an N:N variety to use to filter each other.
Is there any way to get around this? As it is the report is pretty useless when you can't compare side by side expenses and budget. I need to be able to drill down by type and by individual quarter.
Solved! Go to Solution.
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
HI @bkirkey,
Please provide more detailed information to help us clearly know your scenario.
BTW, current power bi not support to direct create M:M relationship, you should add a bridge table to link them and convert to M:1 relationship.
After above steps, if you want to use both side data in one visual at same time, you should summary one side, otherwise it will get error.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
82 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |