Forum Discussion
Multiple Relationships between Tables
I'm having some issues with the relationships between my tables. Essentially I have a Sales table, a Quota table, and a Calendar table. The Sales table is very extensive and based on the way the report is going to be refreshed, cannot be extensively modified. The Quota table is broken into different regions and groups, as there are different quotas for each region and group, as well as for each quarter. Currently both tables connect to the Calendar table, which is used in order to get the fiscal year.
The problem I'm having is that I want to create a gauge chart visual, showing how sales are doing compared to the quotas. Ideally, I'd be able to filter the visual based on region, group, and the fiscal quarter and have both the sales and quota values adjust. But I haven't been able to figure out how to make the tables relate that can accomplish this, since there can only be one active relationship between tables. My tables are currently connected through the calendar table, so when I pick a fiscal quarter, both values adjust properly. But if I want to filter to the region, it only filters one value (ex: the quota value if the region field was picked from the Quota table).
Any ideas on how best to restructure this? Again, the Sales table cannot be extensively modified. I could add some columns if needed, but that's probably about it. And I'm not sure how I'd even restructure the Quota table since the quotas are different for each region, group, and quarter.
Quota table:
Quota Table
Current setup:
Current setup
Anonymous like date table, you should have other dimensions, like region and have a relationship on that table with these sales and quotas tables, and once you do that, you will able to sales and quota for each region and will able to filter from this new region table.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
1 Reply
Anonymous like date table, you should have other dimensions, like region and have a relationship on that table with these sales and quotas tables, and once you do that, you will able to sales and quota for each region and will able to filter from this new region table.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡