Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Morning, I am trying to generate a budget vs actual report but can;t figure out how to set the relationships to work. I have the following structure :-
I have a customer table with customer details in, a budget table with a budget date, amount and customerID and an invoice table with invoice date, amount and customerID. What I want to be able to do is create a table with customer name on the left, months and years across the top and then the sum of invoices and the sum of budget amounts for each customer and each period. Clearly I need to link the customerID to both the budget and invoice table, but then I also need to make sure that I display only budgets and invoices from the same time period. What is the best way to achieve this ?
Solved! Go to Solution.
in short
Delete the relationship between budget and invoices
Get a calendar table and join to budget and invoices
use the customers table and calendar table to slice and dice your visuals
write measures over your budget and invoices table
@ChrisDurham , Do not Join, Budget and invoice, Create common tables like date or any other dimension join both tables with that.
https://www.seerinteractive.com/blog/join-many-many-power-bi/
https://www.youtube.com/watch?v=Bkf35Roman8
Try to be in star schema - https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
@ChrisDurham , Do not Join, Budget and invoice, Create common tables like date or any other dimension join both tables with that.
https://www.seerinteractive.com/blog/join-many-many-power-bi/
https://www.youtube.com/watch?v=Bkf35Roman8
Try to be in star schema - https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
in short
Delete the relationship between budget and invoices
Get a calendar table and join to budget and invoices
use the customers table and calendar table to slice and dice your visuals
write measures over your budget and invoices table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |