Allocation of cost based on calculated cost drivers

[cajste]

Hi,

I'm working on a post calculation model where I for example want to allocate different costs to our customers each month based on different cost drivers. Insurance cost is for example to be allocated to customers based on each customers share of the total number of kilometers produced for all customers a specific month.

To do this I have created two measures:
CALCULATE([Net];dim_Account[AccountNo]=6310) - calculating total insurance cost based on the general ledger each month.

and

SUM(Prod[Km])/CALCULATE(SUM(Prod[Km]);ALLSELECTED(dim_Customer[Customer])) - calculating each customers share of total km.

The first measure has no connection to the customer table but the second has.

The final measure is "Insurance cost per customer", calculated like this: [Insurance Cost]*[Customers share of Km] and the result looking like this:

[TABLE="width: 496"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Insurance cost[/TD]
[TD]Customers share of Km[/TD]
[TD]Insurance cost per Customer[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD] [/TD]
[TD="align: right"]0 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD] [/TD]
[TD="align: right"]6 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD] [/TD]
[TD="align: right"]0 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD] [/TD]
[TD="align: right"]0 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD] [/TD]
[TD="align: right"]25 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD] [/TD]
[TD="align: right"]10 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD] [/TD]
[TD="align: right"]0 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD] [/TD]
[TD="align: right"]28 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 10[/TD]
[TD] [/TD]
[TD="align: right"]4 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 11[/TD]
[TD] [/TD]
[TD="align: right"]8 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 12[/TD]
[TD] [/TD]
[TD="align: right"]13 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer 13[/TD]
[TD] [/TD]
[TD="align: right"]7 %[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD="align: right"]-141 770[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]-141 770[/TD]
[TD="align: right"]100 %[/TD]
[TD="align: right"]-141 770[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]


I assume that the problem is that PowerPivot allocates Insurance cost to the Customer other and would need to have it on all rows? Any suggestion on how to solve the problem?

Brgds,
Caj

 

[cajste]

Just asking for help seems to be a way of solving the problem...

I tried CALCULATE([Insurance Cost];ALL(dim_customer[Customer]))*[Customers share of Km] and that solved the problem.

Any suggestions on better solutions?

//Caj