Distribute cost among cost centers

[mpbc-user]

Good Day Everyone,

My Problem:

We have 300+ Cost centers.

The first 100 are general business expenses , and everything above 100 are contruction sites.

We want to distribute the general business expenses to all the other cost centers.

How do we want to distribute it?

We thought of a formula like:

1. Calculate the sum of costs in this year from all construction sites.

2. table/list? which has (cost contruction site in year)/(sum costs from all contructions in year)

    => our goal is to get how much % the contruction site is "worth"

3. % of general business expenses distributed on contruction site.

   => best case would be if its possible in accumulated sum for a line diagram.

The Line Diagram is built like this:

Green Line: Income from Contruction Site

Red Line: Costs from Construction Site

Black Line: Calculation of (Income - Costs)

and we want that orange line that i have drawn in the picture.

All our bills have the fields

[Date]	[Bill No]	[Cost Center]	[Net]
xx	xx	xx	xx

My first Idea was to make 2 different tables

First Table: Cost Centers below 100

Second Table: Cost Centers above 100

and then with the second table i would make a third table in which are rows like:

[Cost Center]

2018

2019

2020

2021

2022

and then the formula on this table.

But maybe you guys have a better idea how to implement it.😅

And by the way another question😅

i made the diagramm shown above and

now i have to choose Cost Center and Years

do you know a way how it selects automatically the years in which the cost center is in.

And if it is possible to make a line diagramm for every cost center without doing it per hand.

the first idea i had was with the two fields i made.

But like that i don't have a total overview across all cost centers.

Do you have an Idea how to make that?

1 cost center per page, in which the page name is the cost center number?

Is it possible to do it automatic?

Thank you for reading until now!

If I am able to help you helping me with giving more information please say the missing or helping information in the comments, then i will edit this post.

Have a wonderful day!

Greatings.

[v-jialluo-msft]

Hi  @mpbc-user ,

The first question is to calculate "worth" and divide general business expenses evenly into each cost center.

This requires sample files and expected output.
The second question is that you need to paginate the data for each cost center.

You can try adding filters to the entire page, so that each page filters out different content and names it according to what is displayed.

Refer to the links below:

Add a filter to a report in Power BI - Power BI | Microsoft Learn

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.