Budget vs Actual: Zero Complexity Model in Power BI

star schema model

Written by: Reza Rad, trainer, consultant, and mentor at RADACAD.

When I explain the Star Schema and best practices of data modeling in Power BI, often I get questions such as what if we want to have budget and actuals in the same data model? What if there are two tables with different granularity? We cannot solve it with simple data modeling and relationships, this needs complex DAX calculation. My answer is: That can also be solved with a zero complexity model. In this article, I’m going to explain an example of how two tables with different granularity (like budget and actual) can be modeled easily using Power BI.

The Sample dataset used here is the AdventureWorksDW Excel file which can be downloaded here.

Sales Star Schema

A fact table is a table that contains happening of action, it keeps numeric and aggregatable data in it. An example of a fact table is the FactResellerSales table in the model below, which is connected to DimProduct, DimDate, DimSalesterritory, and DimEmployee.

star schema model

To create this model:

  • Get data from AdventureWorksDW data source from above
  • Select all the tables mentioned
  • Remove the relationship between DimSalesTerritory and Dim Employee
  • Set the relationship between FactResellerSales and DimEmployee as Active
  • Create a relationship between OrderDateKey in the FactResellerSales table and the DataKey in the DimDate table

The model above is a perfect representative of a star schema model. This model can easily answer all questions regarding sales. However, it seems difficult to think about star schema when you want to bring another model in with different granularity.

Sales Quota: Different Granularity

For this example, instead of the budget data, I have Sales Quota which is under a different granularity. The concept and principles remain the same. We have a table with different granularity which we want to connect to this model. The Sales Quota has the quota of sales for each employee in each quarter. The granularity of this table is per employee and per quarter, while the granularity of the sales table is per employee, per day, and per combination of SalesTerritory and Product also.

If you get the FactSalesQuota from AdventureWorksDW, you will notice that there is a Date column and the DataKey which I have removed in my example below because they will be confusing for you to understand the table is showing quarterly data.

Here is how the data in the Sales Quota table looks: (FactSalesQuota in the AdventureWorksDW):

star schema model

Challenges of Two Different Granularities

If you want to build the star schema for Sales Quota and Sales (similar to actual vs. budget), then the challenges would be:

  • How to connect the Sales Quote to the date table?
  • If I create a Quarter dimension, then how to connect Quarter dimension to the date dimension? Does this create a snowflake scenario?

I’m going to explain an easy method to solve it and then it will be a proper star schema again.

Connect SalesQuota to the Date Table

Although the sales quota information is quarterly based, you can still connect it to the date table. This would avoid creating an extra dimension for the quarter and snowflake between quarter dimension and the date dimension. The only thing to consider is that you have to consider a specific date in each quarter as your default value. For example, we can consider the first day of each quarter as our data value.

To achieve this purpose we need to create a column in the SalesQuota table which has the DataKey in it (the DataKey used in my sample is in this format YYYYMMDD), so it can be something like 20190101. This is what we’re going to build in this part:

To create that field, you can easily do these steps:

  1. Create month number based on the quarter number
  2. Create the first date of that month as a custom column

To create the first month of the quarter from the quarter number, you can follow a simple calculation of ((<quarter-1>*3)+1), as below:

The calculation above is done in the Power Query Editor.

The second part is to create a Datakey based on it;

To do this part you first need to make sure the month number is a two-character length field, you can do that with these 3 steps: 1. converting the data type to text. 2. adding a prefix of “0” to this column. 3. extracting the right two characters of this column.

Convert the data type to text:

Then add a prefix to the column:

and add zero as the prefix

Then extract the last two characters:


The final result will be:

Now that we’ve got everything needed, the last step is to create a custom column which is the concatenation of Year, Month, and Day (make sure before this step you changed the data type of CalendarYear to text).

And then change the data type of the DataKey to the whole number, you can also do remove these columns: CalendarYear, CalendarQuarter, and First Month of the Quarter.

I haven’t explained the details of each step, because your data might not be at a quarter level and the date key that you use might have a different format. You might even use Merge option in Power Query to connect this to an aggregated version of the date table based on year and quarter and then fetch the first of each quarter from the expand column. No matter how you do it, the main point of all these steps is to make sure you can connect your table to the date dimension without needing an extra dimension.

Star Schema Everywhere

After adding the DataKey to the Sales Quota table, you can connect it to the date table, and also the employee table, as you can see we still have Star Schema everywhere.

There is no need for a bi-directional relationship or connecting a dimension to another dimension. This model can answer your budget vs actual questions easily.

Sample Analysis

To show you some sample analysis I fetched from the Sales Quota vs the ResellerSales in the above scenario, I have now created some visual reports. All these visuals work without needing any complicated DAX calculation or bi-directional relationship. All of that is possible because the data model is set up right.

Simple DAX Measures

I only created two measures for this calculation, very simple measures: Bud vs Act:

and Bud vs Act %:

Sample Reports

And here are the reports:

star schema model

By employee:

star schema model

And the details which are a drill through from the other two pages;

star schema model

Benefits of this Model

I showed you a fully-star-schema model design for budget vs actual in this article. Benefits of this model are basically all benefits of the star schema, including but not limited to:

  • No extra relationship. Direct single relationship between each fact table and each dimension connected to it.
  • No need for bi-directional relationship.
  • No need for many-to-many relationship.
  • No need for complicated DAX calculations.
  • Fast performing model that answers the budget vs actual requirements.

Resource Credit | RADACAD



Jet Global Spring Release

What’s new in the Jet 20.0 spring release

Jet Global has a new product release which was released in April this year. Jet Global officially rolled out Jet 20.0 and we want to tell you about some of the new features and benefits you can expect to see.

At Jet Global, we are always trying to make our reporting, analytics, and data management solutions easier to use. Improvement is a constant. As you might remember from our last product release, we focused on enhancements, reporting speed, updates, and navigation in every product. In the upcoming Jet 20.0 Spring Release, there are three major highlights: Microsoft Word integration, a redesigned browser, and new integrations!

Microsoft Word Integration 20.0

We know there are a ton of customers jumping for joy right now! Available in the Jet 20.0 release of Jet Reports, Word integration enables users to configure Word templates and generate their output from within Jet Reports.

Compared to the current manual process of cutting and pasting reports and charts into Word, this feature will automatically generate word documents. Supplying shortcuts, reducing errors, providing consistency, and enabling real-time data.

All you have to do is click on the “Configure Word Export” button that will be added to the Jet Ribbon. Within the Word export, you can define ranges for merging Jet Reports’ data into Word. As well as selecting what charts to embed.

This new feature will give you a faster and easier way to generate Microsoft Word documents using Jet data. For Jet Designers or regular users that need to produce executive reports, financial statements, cover sheets, invoices, and billing statements, this is a huge time-saver!

This addition is especially important for business process outsourcing (BPO) organizations. Who have to generate up to thousands of “Notice to Reader” financial statements in a standardized, branded template with up-to-date data, and can now have that entirely automated with Jet. Learn more about the Jet BPO offering here.

User Interface (UI) Improvements 20.0

One of the most exciting changes you will notice in the Jet 20.0 release is our redesigned Jet Browser! The user interface improvements are consistent with the Jet Report Wizard design. Everything is more visible and easier to find.

Using the Jet Ribbon, the new UI enhancements include a collapsible details pane on the right and the following table and field improvements:

  • Search filter box
  • Independent table and field favorites
  • Drag-and-drop
  • Table drill down button
  • Fields are multi-select

Once you update to Jet 20.0, you will also be able to take advantage of the new changes made to the insert PivotTable feature. This includes is a more consistent visual design and better navigation to walk you through creating and inserting your PivotTable.

New Integrations 20.0

On top of a redesigned Jet Browser for efficiency, we have also added some new integrations into the Jet 20.0 release.
• The “Dynamics 365 Business Central” data source is now available within Excel, the Jet Admin Console, and Jet Hub.
• Office 365 support for users using Azure Active Directory (AAD).

Keep your eyes open for more updates on Jet Reports, as well as demo videos to help you excel using this key business reporting tool.  Find out more about Jet Reports on our website to see how Jet Global can take your business to the next level. Call us on +27 (21) 205 3650  or email us on info@allonline365.com.


Credit | Jet Global 


allonline365 Newsletter

Call Now Button