Cost allocation

CDLM · October 24, 2018, 6:51am

Hi Fellow Power BI Users -

Please advise how to allocate the warehouse charges to respective departments.

info

I have 2 tables as attached in the above. One table, on the left, shown the inventory amount transferred from warehouse(WH) to various departments and the other table, on the right, was the warehouse charges incurred by the respective warehouses for the departments. Warehouse charge, from the right table, is required to be allocated to departments, based on the amount of inventory that each department has drawn from the warehouse. For eg, warehouse charges of WH1 to be allocated to department 1 is $36,857 (ie ($100,666/565,490) * $207,046). Please advise which measure I should create to allocate the warehouse charges to departments.

Thanks in advance for any help.

Regards,

CDLM

sam.mckay · October 24, 2018, 10:15pm

Ok this to me looks like a scenario perfect for using the TREATAS function.

As you want to join data from one table to another based on a common column.

You could create a Warehouse lookup table but I have moved to more favouring using TREATAS in these situations.

For more on TREATAS see here

So you formula will need to look something like this.

Warehouse Charges = 
CALCULATE( SUM( Storage chg ),
        TREATAS( VALUES( Warehouse Charges[Warehouse], Goodstable[ Warehouse] ) )

Can you try this technique and see how it goes.

If you need to contend with integrating the different months as well, then will need to add to this.

Check out 6:25 in the linked tutorial for ideas around that.

Chrs

CDLM · October 26, 2018, 3:16am

Hi Sam,

Thanks for your prompt response. I have used TREATAS measure as follow:-

 Storage chy by WH = CALCULATE( SUM('Warehouse  charges'[Storage chg] ),
        TREATAS( VALUES( StkTrs[Warehouse]), 'Warehouse  charges'[Warehouse] ) )

and the results as follow:-

Storage charge by warehouse (table at right bottom), was not allocated to departments. I need to allocate warehouse charges to departments based on the transfer amount. Please advise which measure I should create to allocate the warehouse charges to departments.

Thanks.

sam.mckay · October 26, 2018, 6:22am

Ok sure, but what is the logic for the allocation.

In this table here from your example there is not Dept column only warehouse

So if you need to further allocate it in some way, what is the logic behind that?

CDLM · October 26, 2018, 2:48pm

I would like to allocate the respective Warehouses’ Storage chg to the Depts (100-104) in accordance to the proportion of Transfer amt. E.g. 207,046 to be allocated to Dept 100: (207, 046 x 100,666/565,490), Dept 101: (207,046 x 200,250/565,490), etc. Let me know if you need further clarification. Thanks.

sam.mckay · October 28, 2018, 8:25pm

Ok so how I would do this then is first work on an allocated measure.

You need to find a way to go Transfer amount divided by the subtotal amount,

I’m guessing the exact formula here but I don’t think it is too difficult.

For the denominator in this calc you need something like this.

CALCULATE( [Transfer Amt], ALL( DeptColumn ) )

That should be it.

So your allocation formula would be…

DIVIDE( [Transfer Amt],
         CALCULATE( [Transfer Amt], ALL( DeptColumn ) ),
                 0 )

Something like this.

Then you just need to multiply this to you Storage chg measure.

This should do it I believe.

CDLM · October 29, 2018, 3:19am

Hi Sam,

I managed to get the answer by adding the measure as follow:-

Allocated = DIVIDE( SELECTEDVALUE(StkTrs[Transfer amt]),[Denominator],0)*[Storage Chg by WH]

The results as follow:-
Capture

Noted that no summation on the allocated storage charges column. Please advise how I should refine the above measure so that total amount of allocated storage charges could be reflected.

Thanks.

sam.mckay · October 29, 2018, 9:33pm

I have created a few videos around how to sort totals.

See below

The key is to really understand the context of the calculation.

Now if we look above you already have an allocation measure that has created the total. So the idea is to work out a way to replicate this in the final measure.

Would I would try first is to wrap your latest measure in this, I mean the measure - Allocated storage chg.

IF( ISFILTERED( DeptColumn ),
      DIVIDE( SELECTEDVALUE(StkTrs[Transfer amt]),[Denominator],0)*[Storage Chg by WH],
              [Storage chg] )

This is probably the most simpliest way to sort this quickly.

I’m not 100% sure it will work as can’t see the model and test it, but this is my best guess based on what you’ve shown me.

Thanks