Prorating values by business unit AND month

[Baye]

Hi!

I'm with a new project and have a difficult problem I can't get to solve:

I have 3 business units, each business unit has between 3 and 8 sections. Each month I have some movements that are directly assigned to a unit and a section, but there are some movements without a section that must be prorated by the weight their sections sales have in the business unit's total sales during that month. Of course these weights are different each month.

There are also some movements without the business unit (general expenses), these must also be prorated by the sales each section of each business unit has in the month.

The sales movements always have business unit and section, but the expenses only have business unit and sometimes have the section, when the section is informed I must keep the record, when it's blank I must apply the prorate %.

I have one table with the general ledger, each row has date, account number & description, amount, business unit, and (sometimes) sections.

I've tried lots of combinations, cumulative totals by business unit or by section...but can't solve it without making a dynamic table in excel.

Any ideas on how to start?

Any help will be very much appreciated!

Juan

[Anonymous]

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Baye]

Hi @Anonymous 

Apologies for not publishing a model. I just wanted to know how to start modelling, didn't want the work to be done.

Anyway, you can find an example here:

https://drive.google.com/open?id=19Yjr94Og_PbQK2MOUntFTH-nYMReIcZz

As you see, I need to generate as many lines/columns each month as the number of "%" found in each section in that period.

Best,

Juan

[Baye]

No ideas of how to lay out this model? Thx

[stevedep · Accepted solution]

Hi,

I have a solution for you, this involves doing most of the work in the backend (Power Query). This includes the following steps:

1. Get the sales ratios for the sections (to use later on). 

1a. Get the total sales amount for a BU in a period. 

1b. Join the total with the original rows to calculate the ratio (amount / total amount for BU). 

2. Make a table with all the BU's en sections. 

3. Group by the expense type, period and BU and sum the amount. 

4. Join with the BU and Sections so each expense type has a section. 

5. Multiply the total amount by the ratio (derived from sales). Giving the individual values for the section for each acc type. 

Please note, this covers the scenario of missing sections. To cover missing BU's you can apply the same logic and approach. 

Solution is available in this file. 

Some screens to show the results:

If this is what you are looking for please mark as solution. Thanks!

Kind regards,

Steve. 

[Baye]

Hi @stevedep 

GREAT! Nice solution, It works perfect!

I've done this:

1. Group sales by date and section, summarizing amounts and calculating ratios. For this i had to group all the sales in the month using this formula: Date.EndOfMonth()

2. Create a table with sections and BU and combine it with the previous one (1).

3. Combine the sections table with the accounting ledger, this combination of tables has created all the lines needed. I started with a 5.000 row table and now I've aprox. 40.000 rows as every expense row is now divided into all the sections that has sales in the month. THIS WAS THE MAIN PROBLEM FOR ME. Here i came up with a solution with an "if then else" formula that brings the correct % to prorate and multiplies it by the Amount column, obtaining the final prorated value.

(for the combination I've used a "DATE_BU" column in both tables, setting date as text using Text.From([DATE])&"_"&BU column

The rest of what needs to be done is easy but I'm super grateful for this idea.

Problem solved. Thank you very much!

[stevedep]

Welcome! Glad to be of help.

[stevedep]

Hi,

I have a solution for you. I suggest to do most of the work in the back-end (Power Query), whereby you:

1. Calculate the ratios for a given BU & Section in a period based on the sales.

1a) Sum the sales amount per BU per period.

1b) Join with the original set to calculate the sales ratio for the section (contribution).

2. Create a table with BU's and Sections.

3. Join the table with BU's&Sections with the totals for sales, expenses, etc. This will expand the rows so each acc type will have all the sections. 

4. Calculate the individual values / amounts for the sections by multiplying with the ratio value from step 1b.

Here you see the overview of high level steps:

This shows the output:

Which is available in this file. 

As you will see I have not covered the scenario of missing BU's. But you would build the same logic but now with ratios on the total level. As you did in your spreadsheet. 

I also summed up expenses rows for the same BU in the same period, which makes sense I believe. 

I hope this helps you, if so, please mark this as the solution. 

Kind regards,

Steve.