Forum Discussion
Helper IIJoin and model the data with different granularities
Hi kobkabnaja
Your question has soved at another post, so you can consider to close this post, and you can offer solution in this post so that more user can refer to.
Best Regards!
Yolo Zhu
Super User- Link datasets via a common field (like Department, Sub-department, etc.).
- Since the budget is at a yearly level and expenditures are daily, summing the budget in a one-to-many relationship will lead to inflated numbers. You can resolve this by creating a relationship between the two tables using the Department (or similar) field and then using measures to bring the budget into the visual without duplicating it.
- Measure:
YTD Expenditure = TOTALYTD(SUM(Expenditures[Amount]), Expenditures[Date])
Yearly Budget = MAX(Budget[Amount])
- Once these measures are created, you can plot both YTD expenditure and the yearly budget on a bar chart, using the department (or other relevant dimension) as the axis.
If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
kobkabnajaKedar_Pande Thanks for your quick response! I have a quick follow-up question. When linking the datasets, should I join them column by column using a many-to-many relationship, or should I combine the relevant columns into a unique key and use a one-to-many relationship?
I’d appreciate any further thoughts or comments you may have.
- Kedar_Pande
it's generally more efficient to avoid many-to-many relationships unless necessary.
Use a one-to-many relationship with a unique key.- kobkabnaja
Thanks Kedar_Pande ! Although I have not achieved what I want, your solution is super helpful!