Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Baye
Frequent Visitor

Prorating values by business unit AND month

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

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

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:

prorate2.pngprorate.png

 

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

 

Kind regards,

 

 

Steve. 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Frequent Visitor

No ideas of how to lay out this model? Thx

stevedep
Memorable Member
Memorable Member

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:

prorate2.pngprorate.png

 

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

 

Kind regards,

 

 

Steve. 

 

Baye
Frequent Visitor

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
Memorable Member
Memorable Member

stevedep
Memorable Member
Memorable Member

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:

prorate.png

 

This shows the output:

prorate2.png

 

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. 

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.