Allocating costs form one deartment to another

[Kaizu80]

Hi there!

I have a P&L-report in Power BI and what is needed to be set up is:

- company total that includes 4 departments  - this is done

- department total with 2 of the (Sales) departments "carrying" the costs of the two other departmens (Admin&Common)

I have the company total working fine and the department levels also, but... I need a measure (or column) that collects the costs from the two non-profit departments and and show them as a value in the two departments that are supposed to carry the company result.

The most simple way is to divide my measure "Gross Margin" with predetermined percentages, but I do not have the knowledge on how to get the allocated costs show up when I have the sales department filtering the P&L statement.

Thank you for the help!

Kaj 

[v-huizhn-msft]

Hi @Kaizu80,

It's hard to reproduce your scenario without sample data, you'd better post some sample data, so that we can post the solution which is close to your requirement.

Thanks,
Angelia

[Kaizu80]

Hi!

I can't screenshot the original data, because it immediately reveals the company names etc. But here's how the data basically looks like.

- The account 4000 is already counted together right as a measure "Variable costs" in my P&L statement

When no department filter is chosen the amount is right. The same goes with the departments selection "Common".

But what is needed is that when you choose the filter department "Sales", it shows of course the original data from the books that are directly posted to that department and adds the predetermined percentage of the Variable costs of the department "Common".

So interaction is what I need. A measure that will not be counted/shown when no department-filters are chosen and when some filters are chosen, the amount will be taken into account in the P&L statement.

This might be something that will be solved with HASONEFILTER, ISFILTERED functions, but I cannot figure out how.

[v-huizhn-msft · Accepted solution]

Hi @Kaizu80,

I reproduce your scenario get expected result, please see the steps below.

1. Create a new table including Department, please confirm there no relationship between Department table and your resource table. Here, I type your sample table in Table3.

 

2. Create a measure using the following formula.

Measure = IF (
    HASONEVALUE ( Department[Department]),
    SWITCH(FIRSTNONBLANK(Department[Department],Department[Department])
    ,"Common",CALCULATE(SUM(Table3[Debet]),FILTER(Table3,Table3[Department]="Common"))
    , "Sales",(CALCULATE(SUM(Table3[Debet]),FILTER(Table3,Table3[Department]="Sales"))+ (CALCULATE(SUM(Table3[Debet]),FILTER(Table3,Table3[Department]="Common"))*0.56)))
    , SUM(Table3[Debet])     
    )

3. Create a slicer including Department[Department], table visual including Department[Department],Table3[Account], Table3[Measure] and Table3[Credit]. Please see the following screenshot. There is no filter in slicer shown in first picture, there is "Common" filter shown in second picture, and there is "Common" filter shown in third picture.

Picture1Picture2Picture3

Best Regards,
Angelia

[Kaizu80]

Thank you Angelia!

That works just the right way. Next time you visit Finland, I'll buy you a cup of coffee

[vanessafvg]

@Kaizu80 its so much easier to work out what you are asking if you provide screen shots of your data and what it is your expect?

[Kaizu80]

Sorry, of course it would be easier.

I have a measure that basically has the needed data:

Allocatable costs = CALCULATE([GROSS MARGIN];FILTER(Departments;Departments[Department]="Common"))

From somewhere I have picked up a Measure that works pretty good, but the wrong way 🙂

Allocated, sales = if(OR(hasonevalue(Departments[Department])=False;values(Departments[Department])="Sales"); [Allocatable costs]*,56)

That calculates the right amount but what I want is to show and use the result in the P&L statement when the Filter "Sales" is chosen.

[vanessafvg]

@Kaizu80

i am confused as to why this wont work with a filter what issue are you getting?  can you provide screen shots of how  your data is laid out?

[Kaizu80]

One example:

The company total Variable costs are 500k€.

When no Department-filters have been chosen, the costs should be 500k€.

The Department "Common" has 200k€ Variable costs.

The "Sales" department has 150k€ Variable costs.

When the Department "Sales" is chosen as a filter, the Variable costs are: 150k€+(200k€*56%)

Kaj

[vanessafvg]

@Kaizu80

so are you saying when a filter with sales is selected it should be this calculation

salesvalue€+(commonvalue€*56%)

where does the 56% come in, is the same everytime?

[Kaizu80]

@vanessafvg Yes, just like that. When "Sales" is selected, it should calculate like that.

The value comes from budget numbers and will stay the same everytime.

The figure is someting like: the percentage of company total variable costs that the Sales department uses for it's operating process.