Equal Allocation of Budget in Power BI

The equal allocation of budget is the technique you use to compare budget information at the different granularity with other data such as Sales or Discounts etc.using Power Query. They are an extension of the handling of different granularities in Power BI using the DAX as I discussed in my last blog. In this article, I will use Power Query to develop Equal allocation pattern to allocate the budget at day granularity for which it is not available.

Last Week, I had to deal with the similar problem from my client where they would like to allocate the monthly budgets equally at the day level and this would require us to write custom MDX SCOPE Statements in the cube. This led me to the thought that how can we achieve this in Power BI. Do you want to explore how I did it?

Let’s join me in this Power BI Journey to learn this new technique.

Suppose you have budget for each product at the month level, as shown in Figure 1.

and I would like to achieve the final result, as shown in the Figure 2.

To get this end result, I have first created a Calendar Table in Power Query.
In the Blank Query, Open the Advanced Editor, Copy and Paste the below code to create a Calendar Table.

Now, We will add few new columns (BudgetID, MonthNo, MonthName & Total Days) in the Budget Table.

To allocate the monthly budgets at the day granularity in the Budget Table, I have created a new custom column by simply dividing the BudgetAmount with TotalDays.

Next, I have merged the Calendar and Budget Table with Month Name as the join key, as shown in the below figure.

I then just need to click on the expand icon next to the column header of the Budget Column to repeat each equal allocation amount for all the days in specific months it applies to, rename the columns and set the column types appropriately.

Last of all, I need to create day column in the Calendar Table by duplicating the Date Column & converting it into the Day column. This is required to use the Sort by Column functionality to sort the Date Column. Finally, format my Allocated Amount column using a dollar sign.

Now, I’m ready to load the data into the Power BI.

One last point, Disable the load of the Budget Table in the Data model. This will make your data model cleaner and clutter free.

You can also create a Year Hierarchy in the Calendar Table and use the Matrix Visual to display the correctly allocated values in the Power BI.

This technique can also be extended to equally allocate the Year/Quarterly budgets at the lower granularities.