Allocate quarterly budget


#1

Hi,

In my budget sheets I have sales by quarter
Q1-2019
Q2-2019
Q3-2019
Q4-2019
Then I want to split it out on a monthly granularity.
Jan 50% of Q1, Feb 25% of Q1 and Mar 25% of Q1. Then doing the same for the other quarters. Any suggestion of how I should do? I am trying With Cross Join but I dont get it to work.

Thanks
Hans


#2

The key to this is to work out the budget allocation algorithm.

I’ve covered this a few times.

In theory if you have a quarterly number you need to divide this by 3 for months and then by 90-92 days if you want this at a daily level.

Here’s some links around this.

Check out the techniques used here. You will need something similar, with slight adjustments for quarterly numbers

Also check out this tutorial as well.

This is a key function that I think you’ll find very useful in this particular scenario.

And here another good example

If you can master the ideas and concepts here you’ll be able to solve for what you need.

If any questions on this let me know.

Also if you can add images of the data and model when completing these, that can really help also.

Chrs!


#3

AllocationDNA.xlsx (10.1 KB)

Please find attached file. There you can see how I want it to be.

Thanks.

Best regards

Hans


#4

Ok this is quite different and there’s quite a bit to it. Quite a unique scenario also. But also a really interesting one.

This is made a little more complicated by the Q3 breakdown. As it’s not generic, this needs special attention.

I actually covered a similar technique here at the recent learning summit

This used a very similar allocation methodology but was at a yearly level.

I got this into Power BI and created a model

You need to keep the data a bit more uniformed to get this to work.

Check out what I completed here

image

No 2019s in this table anymore. This way we are able to compare it to the allocation table on a like for like basis

I think I’ve been able to get it all going here

Answers seem to be coming through.

Check out the attached for the individual formulas. They are all pretty simple.

I use TREATAS once. This is a key function for this.

Attached
Allocate Quarterly Budgets.pbix (60.1 KB)


#5

Hi,

Amazing, both for the answer and having the opportunity to ask an expert :smile:

Then I only need to figure out how to make sure the total sum in the column budget allocation gets correct. But I guess there is some video on that.

Again, thanks. I would never had figured it out by myself.

/Hans


#6

Great.

Is the total not correct right now? It looks like it to me, so not too sure. Let me know.


#7

The column Budget allocation sums up to 417. But the correct one should be 1000.


#8

Are you sure it’s meant to be 1000 after the allocation percent is added to it?

It’s looks like to me after you take the budget breakdown and multiply that by the allocation percent you will get a lower number than 1000?

Are we comparing the same thing here?


#9

Yes, I am sure. I only want to split out the budget to month, but the total should be the same. For client AA it is (100+200+20+80+30) = 430. But you are getting 179.

You have the correct numbers in Budget allocation column, except the sum amount in the end. If you sum up the numbers for client AAA it is:
50
25
25
100
50
50
20
56
24
15
8
8

431 (1 different due to rounding)

But you are getting 179 if you filter. On total level its should be 1000. I just want to splitt the budget into months.

Thanks.


#10

Sorry my bad on this, I see the issue now.

Definitely the total is the problem here.

We need a formula that works a bit different to manage the total.

Try this to get the right total. I’ve kept the solution pretty simple but think it works well.

Budget Allocation = 
IF( HASONEVALUE( 'Time Frames'[Month] ),
    [Budget Breakdown] * [Allocation Percent],
        [Budget Breakdown] )

Let me know if this works for you

Chrs


#11

Cool :smile:

It Works perfectley!!!

Many thanks.


#12

One more question: Whaft if client AA have another Budget allocation than BB and CC, how should I do then?


#13

You’ll probably have to work up an example for review on this.

To solve it will be a combination of the right model setup and also the correct DAX formula.

It brings in another aspect to the calculations that needs to be managed correctly. Will need to set the exact setup of the table to know what is the correct solution on this.

Thanks


#14

Sure. Please find Attached file.AllocationDNA with client splitt.xlsx (10.9 KB)

The file you showed before worked Perfect, after the small formula change you also sent. I want the same except the allocation should be on Client Level.

Thanks.


#15

Check out the update made to the attached

Allocate Quarterly Budgets.pbix (64.5 KB)

I think was quite easy in the end.

Just needed to add the updated allocations to the table that was already there and then join up the client table to the updated allocations table.

See below

image

This is the benefit of building a robust model like this.

All the formula seem to still work as they should with the additional information

image