Creating a relationship in Powerbi

[PradipMCT]

Hi everyone,

I'm working on a Power BI model to analyze training costs and compare them to allocated budgets. I have data in separate tables for:

• Employee Master
• Training MIS (Management Information System)
• Date
• Training Budget (by location)
• Training Expense (by location)

The tables have follwoing columns:

Employee Master: Employee ID, Employee Name, Location
Training MIS: Employee ID, Training Program ID, Training Program Name, Training Program Date
Training Budget: Location, Training Budget, Date
Training expenses: Training Expenses, Location, Date/month of expense

My Current Approach:

I've established the following relationships between these tables:

1. Employee Master (one-to-many) Training MIS
2. Training MIS (one-to-many) Date
3. Date (one-to-many) Training Budget
4. Date (one-to-many) Training Expense

My Question:

I'm unsure if this is the most efficient way to model the data for my analysis. Specifically, I'm wondering:

• Should I introduce a separate "Location" dimension table?
  • If so, how would the relationships change?
• What are the best practices for calculating metrics like:
  • YTD Training Expense
  • Total Training Budget
  • Percentage of Budget Spent on Training (YTD)

Desired Outcome:

I want to be able to effectively analyze training costs across different locations and compare them to the allocated budgets

[PradipMCT]

I think i framed my question incorrectly. What i wanted to ask primarily was the way relationship to be made and not more focus on the dax measure. what are the best practices to create the relationship to get those dax measures correctly.

[Anonymous]

Hi, @PradipMCT 

Thank you very much for your reply and am very sorry for my late reply. On your first question, do you need dimension tables:

The golden rule of design is to make dimension tables wide enough, which can make dimension tables have powerful filtering capabilities. The more fields in a dimension table, the greater the ability to slice and dice the data.

In your scenario, if you need to filter data from different regions or some other operations, then having dimension tables is a great option.

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

The relationship you create should be based on your actual situation. Once the relationship is established, you can filter the data by fields between different tables. You can leave the links below, which have a detailed description and comparison of each relationship:

Many-to-One or Many-to-Many? The Cardinality of Power BI Relationship Demystified - RADACAD

Also, it is recommended that you read these articles of this blog as shown in the image below. This will be helpful for you to understand relationships, dimension tables, and fact tables:

Build Your First Star Schema Model in Action: Power BI Modeling Basics - RADACAD

Regarding your second question, best practices for calculating metrics, you can refer to the following article. In these articles, there are various KPIs that you should use to choose the right solution for you. 

In these samples, you can download them that are similar to your scenario. Open them in Power BI and learn about the different practices of metrics.

First, the official sample provided by Power BI:

Get samples for Power BI - Power BI | Microsoft Learn

Here are some of the practices you might be referring to:

Top 9 Power BI Dashboard Examples (zebrabi.com)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @PradipMCT 

Thank you for reaching out with your query on creating an efficient data model in Power BI for analyzing training costs and comparing them to allocated budgets. Based on the details you've provided, I have a few suggestions and best practices that could help streamline your analysis.

As @PijushRoy  said, you can use Power BI built-in time intelligence function TOTALYTD. Use a measure to calculate the Year-To-Date (YTD) expense. DAX formula example:

YTD Training Expense = TOTALYTD(SUM(Training expenses[Training Expenses]), 'Date'[Date])

Total Training Budget:

A simple SUM measure can be used here:

Total Training Budget = SUM(Training Budget[Training Budget])

Percentage of Budget Spent on Training (YTD):

First, calculate the YTD Training Expense and Total Training Budget as mentioned above. Then, use another measure to calculate the percentage:

Percentage Spent = DIVIDE([YTD Training Expense], [Total Training Budget])

You can use your measure in the table visual, add locations to the slicer, and display the corresponding results by selecting different locations. I hope this helps set you on the right path for your analysis.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @PradipMCT 

Your exisitng data model looks good.
Yes, you can Add location DIM table and connected with other table to analyse the data.
YTD Calculation - https://learn.microsoft.com/en-us/dax/totalytd-function-dax

https://dax.guide/datesytd/#:~:text=In%20order%20to%20use%20any,the%20days%20in%20this%20range.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin