Budget VS Sales

A common business case is to compare your sales number with budget. There are many blog posts about this already. See here for Rob Collie’s post on this topic.

Today I want to show you how you can do this easily with the function of adding calculated table in Power BI desktop. With this functionality you can add calculated table to your data model and treat it as normal table, add relationship to it and do your analysis as usual. In our case we will load some sample sales data (at day level), use calculated table to add a Sales by Month table, then compare this with budget data (also at month level). You can find the New Table button in the Data view ribbon under Modeling as shown below:

Calculated Table In Power BI Desktop

The model is very simple. I loaded sales data (at day level) and budget data (at month level). I added Sales by Month table with DAX function

=
FILTER (
ADDCOLUMNS (
VALUES ( ‘Calendar'[YearMonth] ),
“Sales”, CALCULATE ( SUM ( Orders[Sales] ) )
),
[Sales] > 0
)

I added calendar table with new DAX function CALENDARAUTO(). What it does is based on the dates in your data model, it will add one column table with dates from min dates to max dates in your data model. Very cool and handy. When you have new sales data coming in, your calendar table will update too. Neat! You can see the model in the below picture.Budget VS Sales Model

Lastly I created some simple VIZ to compare sales and budget as show below. Power BI desktop is still evolving and Microsoft is releasing new updates on a monthly basis. As time goes by I believe it will offer the same charting power as Tableau.

Budget VS Sales VIZ

You can download my sample file here for closer look what I have done.

Advertisements

One thought on “Budget VS Sales

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s