Hello,
I am currently working with a power query (not PowerBI) and I have three tables, Acct-Units Table, Actuals-Table, and Budget-Table. I am able to create a one to many relation ship with Acc-Units Table being my 1 and both the Actuals-Table and Budget-Table being my many. However, when I try to create a power pivot table/chart to compare the budget numbers vs the actuals, it shows me there is no relationship. Is there a key i can create a key to connect the two? Or would this be a measure instead?
Thanks,
Michael P.
I know I could merge the tables if need be, but I believe there is a way to better handle this, just not sure what it is. Thanks.
To provide additional context:
Both my actuals and budget table are connected with my acc-units table by acc-unit column which identifies the type of dept. But both my actuals and budget can have multiple transactions for one department, several transactions in one month for the fiscal year. I also have a column in both my actuals and budget table for GL-accounts, but again one department can have multiple GL-accounts. Hope this helps without over explaining myself.
Hi Michael,
The units table sounds like it's a fact table, not a dimension table. I recommend you create a proper dimension table for the accounts and create relationships between this and your 3 fact tables: units, actuals & budget.
When you build the PivotTable, the account field MUST come from the dimension table, not the fact tables.
You may also need other dimensions if you want to aggregate the data by other fields.
Mynda
P.S. I recommend posting your question in one go because questions that don't have replies are flagged as 'unanswered' and are more likely to get a response than a question that appears to have replies to it. If you forget to mention something, then it's best to edit the post than to post a follow up.
Thank you Mynda. Will also follow the rules of posting. 🙂