Elimination of "internal business charges"

[wannalearn]

Hey MrExcel forum,

I am trying to apply a measure that is filtering out "internal business charges". Simplified example:

[TABLE="width: 496"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD]Partner[/TD]
[TD]Cost Element[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD]Labor cost[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Marketing[/TD]
[TD]Labor cost[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD]Sales[/TD]
[TD]Labor cost[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD][/TD]
[TD]Labor cost[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Production[/TD]
[TD]Labor cost[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD]Sales[/TD]
[TD]Labor cost[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD][/TD]
[TD]Labor cost[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

What we see are "internal business charges" i.e. Production bills sales for 50 labor costs. Empty partner fields mean that the costs originate from that cost center. The total allocated labor costs are as follows:


[TABLE="width: 286"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD]Partner[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]

What I however would like to see are the total original labor costs. So i.e. for production this would be 200. Furthermore I would like to have a data slicer in place for the Cost centers. So this elimination would have to be dynamic.

Suppose I selected the production and the sales cost center with this slicer. The result would be:


[TABLE="width: 292"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD]Partner[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Marketing[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD](empty)[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Production[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Sales Total[/TD]
[TD][/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD]Sales[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD](empty)[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Production Total[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

So I would somehow have to eliminate the +/- 50. In my non-dax language way of expressing this it would sound like:

"... if a partner also exists in the currently selected cost center table then flag it..."

I have googled my way around but I guess I do not use the proper words to find a solution for this.

I hope I have properly described the problem. Thanks a lot for thinking about this.

 

[julianwi]

Have you created a second table for the list of cost centers? This should work: FilteredInternal=Calculate(Sum(Allocation[Value]);ISBLANK(Allocation[Partner])) - you would just have to use the field cost center from your new lookup-table to slice the data.

 

[wannalearn]

Hey Julianwi,

thank you for your help. If I understand it correctly than the measure above would always sum up all values that have no partner specified. This would leave me with the original costing data of the cost center selected. This is already interesting for me.

However consider the following example:

[TABLE="width: 325"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD]Partner[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD](empty)[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sales[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD]Production Total[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD](empty)[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Marketing[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Production[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Sales Total[/TD]
[TD][/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

In this Case I selected the cost centers production and sales with the slicer. There is some internal charging (50 from production to sales) and some charging in (sales receives 100 from marketing). I want to flag the internal (internal = in between cost centers that are currently selected so in this case production & sales) charges. The charges received or send from outside the selected cost centers should not be flagged.

Thanks again for your help. It really bugs me that I have not found an answer to this yet .