Calculation for Actual vs Budget Values

Hi Anonymous 

You don't have to create a concatenation between the Location, Product and Month to create a common column for building the relationship. Instead, you can use the current columns. 

You can first read the following articles to learn about the star schema for modeling. 

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

In your case, you can treat "Product Master" and "Location Master" as dimension tables and treat "Sales Transactions" and "Budget Master" as Fact tables. Create the following relationships:

• Product Master: [Product Code] --> Sales Transactions: [Product ID]
• Product Master: [Product Name] --> Budget Master: [Product]
• Location Master: [Location Code] --> Sales Transactions: [Location Code]
• Location Master: [Locatio] --> Budget Master: [Location]

As the current budget data is based on month, you can add a "month start date" column to the budget table. In addition, add a Date table to your model. Date table is a dimension table too. Then create relationships:

• Date: [Date] --> Sales Transactions: [Date]
• Date: [Date] --> Budget Master: [Month Start Date]

The dimension tables should have all distinct possible values, so all possible locations should be there in the "Location Master" table. When you create visuals, always use columns from the dimension tables on x-axis. Then use sales and budget values from the fact tables on y-axis. 

In addition, I found some blogs that should be helpful:

Budget vs Actual: Zero Complexity Model in Power BI - RADACAD

Budget vs. Actual Model in Power BI: Calculations and DAX - RADACAD

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!