Forum Discussion
Calculation for Actual vs Budget Values
Hi,
My data contains majorly the following tables and the columns in those tables are:
Sales Transactions: Date, Bill No., Product ID, Location Code, Qty, Value
Product Master: Sr. No., Product Code, Product Name
Location Master: Sr. No., Location Code, Location
Budget Master: Section, Location, Product, Month, Sales - Budget
I want to carry out the comparison between Actual Sales and Budget Sales.
There isn't a common column between the Sales Transactions and Budget Master. For building the relationship, one needs to create a concatenation between the Location, Product and Month in both the tables.
However, few of the locations are there in the Budget Master but we don't have the actual sales values for those locations. I want those locations to be visible in the visual with a zero value for actual column and the corresponding budget value.
Need help on showcasing the Actual vs Budgeted Sales comparison with the logic explained above.
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!
1 Reply
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!