Distributing values based on Mapping.

Blake101

Hi All,

I have a module MOD1 which is dimensioned by Product Group, Customer and Time and has line item Cost.

I have another module MOD2 which is dimensioned by Product, Customer and Time and has line item Sales.

I have a mapping between Product Group and Product Lists. Every Product Group has multiple products.

I want to allocate the cost from MOD1 into MOD2 based on the Sales weightage in MOD2. The weightage needs to be calculated based on sales and then be used to find cost allocation.

For example,

Looking forward to your suggestions, thanks

Prajjwal88

Hi @Blake101 ,

For achieving this calculation, you can follow the steps below:

1. Aggregate Sales at the Product Group Level:
   Create another line item in your MOD2 module to aggregate sales at the product group level. To do this, you can create a subsidiary view in same module or a separate module dimensioned by Product Group and Customer and use a formula like: MOD2.'Sales'[SUM: 'SYS Product-PG Mappong'.Product Group]

2. Calculate Weightage Percentage:
   Divide each product's sales value by the aggregated sales at the product group level. This will give you the weightage percentage for each product. Example: Sales / Sales on PG[LOOKUP: Product Group]

3. Allocate Cost:
   Use the weightage percentage to allocate the cost from MOD1 to each product in MOD2. Multiply the cost of the product group by the calculated weightage percentage. Example: 'MOD1'.Cost[LOOKUP: Product Group] * Weightage



Please refer to below attached screenshots for further reference,

I hope this helps. 😄

Blake101

Thanks a lot @Prajjwal88 for this detailed solution , it works !