Join and model the data with different granularities

[kobkabnaja]

Hi Community,

I'm new to Power BI and would appreciate any guidance on handling data with different granularities. I've done some research but still feel unsure about the best way to approach this.

I have two datasets: one that tracks daily expenditures and another that represents a yearly budget. My goal is to create a bar chart comparing Year-to-Date (YTD) expenditure against the allocated yearly budget in different departments. The expenditure data contains fields like date, department, sub-department, fund, and category. The budget data has a similar structure but is in the yearly basis.

Here are my questions:

1. To plot these in the same chart, do I need to join the two datasets?
2. If so, how do I avoid having multiple duplications of the yearly budget data in the daily expenditure dataset (since the relationship is one-to-many)? How can I display the YTD expenditure and yearly budget on the same chart?
3. I've come across suggestions to use DAX to retrieve the most recent budget value instead of summing it. Is this a good approach?

I’d really appreciate any advice or suggestions you can offer. Please feel free to ask for more details if needed to better understand the issue.

Thank you!
K

[Anonymous · Accepted solution]

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

[kobkabnaja]

The solution to this question is provided in the post referenced above.

[divyed]

Hello kobkabnaja

This can be achieved through creating a virtual relationship at run time. I would suggest using TREATAS function to create virtual relationship between tables with different granularities. I hope this will help.

Cheers

[TomMartens]

Hey @kobkabnaja ,

this article explains how to build your semantic model if fact tables have different granularities: https://www.daxpatterns.com/budget/

You have to create at least on dimension table "Calendar", or how however you call it. This table connects to both fact tables. Most likely you need to create a measure that prevents the duplication of the the budget value, e.g. dividing the yearly value by 12). But then you also use the word allocation. As alway it depends, if allocating a yearly budget to monthly values is part of your business process than of course it makes sense, if not then I would not recommend to allocate a yearly value to the month granularity just for the sake of having similar granularity. From my understanding this would create a third fact table, but depending on the granularity of the budget value it is also possible to create a new column in the expenditures table.

Consider to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the pbix file to OneDrive, Google Drive, or Dropbox and share the link.

Regarding the chart, the below chart is leveraging the "" chart type, the line represents the averaged monthly Sales Amount, this is done for demonstration purposes. The average value can represent the yearly budget value (value divided by 12):

Hopefully, this provides some ideas of how to tackle your challenge.

Regards,

Tom

[kobkabnaja]

Thanks @TomMartens for sharing very useful resource! It is a little bit different from my case, but worth reading it!

[Kedar_Pande]

@kobkabnaja 

1. Link datasets via a common field (like Department, Sub-department, etc.).
2. 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.
3. Measure:

   YTD Expenditure = TOTALYTD(SUM(Expenditures[Amount]), Expenditures[Date])

   Yearly Budget = MAX(Budget[Amount])

4. 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

[kobkabnaja]

@Kedar_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]

@kobkabnaja 

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!

[gaurav-lakhotia]

Hi @kobkabnaja ,

1. You have to create a relationship between these two tables by creating a composite key, May be using Year(Date), Sub-category & Department.
2. To avoid duplication, you can calculate the daily budget by dividing the yearly budget by number of days in the year (as p[er the business logic).
3. Relationship using a composite key can help here. Ofcourse, DAX would help alot here.

Let me know need further help.

Did I answer your question? Mark my post as a solution!

[kobkabnaja]

Thanks @gaurav-lakhotia! This is a bit off from my case.

[AnalyticPulse]

hello @kobkabnaja 
when you reference two columns from diffrent tables in visual or measure then you can to create relationship between those tables in model relationship tab in powerbi, otherwise your visual will show same amount on each bar or columnof that chart,  when you create relationship between two tables that table the those two tables will act as one whole table, join the tables based on common feilds column(we call is primary key and secondary key) it could be your transaction id or the order id(you have to check it).
create two measures for YTD expenditure and yearly budget and drag and drop those two measures in value feils of the bar or column chart , you will see column or bar for both categories.

let me know if you have other doubts.
learn more about powerbi:
https://analyticpulse.blogspot.com/
PowerBI Basics

Support on Youtube:
https://www.youtube.com/@AnalyticPulse
https://www.youtube.com/@TheRisingCitizen