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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors