Measure to allocate% of whole in PowerPivot

[francoisdublin]

Hello

I wonder who's come across this and found a solution?

I am looking to prorate costs in one table based on quantity in another table but can't figure out how to create a measure for this allocation.
Any hints or insights? Thanks in advance.

Example Calculation
In the example below, for January 2014,

• The Cost table shows a total Cost incurred for Apples of 110. This is the cost to allocate per channel.
• The Sales table shows that 10 apples were sold through Retail and 25 through Supermarket i.e. 29% and 71% respectively.
• The cost allocation therefore is 110*29% and 110*71% respectively.

Sample Data
Downloadable workbook with data and pivot
e.g.

Cost table
[TABLE="width: 267"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Product[/TD]
[TD]Cost[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 267"]
<tbody>[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Bananas[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Kiwis[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Bananas[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]02/01/2014[/TD]
[TD]Kiwis[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Sales Table (for Quantity)
[TABLE="width: 373"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Channel[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]10[/TD]
[TD]Retail[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[TD]Supermarket[/TD]
[/TR]
[TR]
[TD]02/01/2014[/TD]
[TD]Kiwis[/TD]
[TD]5[/TD]
[TD]Retail[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Kiwis[/TD]
[TD]6[/TD]
[TD]Supermarket[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Bananas[/TD]
[TD]10[/TD]
[TD]Retail[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD]Apples[/TD]
[TD]20[/TD]
[TD]Supermarket[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi FrancoisDublin,

Firstly, I add a formula to your first table (cell E5 and down), summing up the total quantity of Table 2 for that product in that month (in case of apples in januari that would be 35), I named this column Tbl2_ProdDateQua.
=SUMIFS(Table2[Qty];Table2[Date];[@Date];Table2[Product];[@Product])

Then I can add another formula to that same table, calculating just the costs for retail (cell F5 and down):
=SUMIFS(Table2[Qty];Table2[Date];[@Date];Table2[Product];[@Product];Table2[Channel];"Retail")/[@[Tbl2_ProdDateQua]]*[@Cost]

In my table, that shows 14,3. When I do the same for supermarket costs, the answer for the first row is 35,7.
=SUMIFS(Table2[Qty];Table2[Date];[@Date];Table2[Product];[@Product];Table2[Channel];"Supermarket")/[@[Tbl2_ProdDateQua]]*[@Cost]

Adding up the costs per line (e.g. with a pivot table) should give you your total costs.

Hope that helps, if not, please don't hesitate to respond,

Koen

 

[francoisdublin]

Hi Koen,

Thanks for taking the time to look into my question. Your solution is simple and elegant and more importantly, it works.
I have spent so much time trying to solve this, which is only part of a larger problem, and this very simple SUMIFS solution completely eluded me until I read your message.
A million thanks,

Francois

 

[mattdete]

could you upload the completed file?? it would help me out a lot.

 

[Rijnsent]

Hi Matt,

try this location: https://www.dropbox.com/sh/l7ywfwzfk5j20sr/dJ6Jk0NGyM , the file is Fruity.xlsx

Cheers,

Koen