Dynamic breakdown of product costs and revenues and margin calculation

[Reroo]

Hi, everyone.

I am trying to implement a specific calculation where I feel stuck und don't know how to proceed further.

So the objective is to calculate different margins for individual products. For that I need to distribute corresponding costs and revenues across products. 

Here is my data design. I have two tables - accounting data with bookings and volume sales. Here I can analyse volumes in detail, however I am having trouble to allocate costs and revenues of one product where I cannot asign accounting bookings to products.

Bookings table

revenue Product 1

revenue Product 2

revenue Product 3

revenue Product 4

costs Product 1

costs Product 2

costs Product 3

costs Product 4

Volume sales table

volume product 1

volume product 2

volume product 3.1

volume product 3.2

volume product 4

So, so it's impossible to distribute costs and revenues further across its subcategories, although the corresponding volumes are available. I assign revenues and costs through accounts table where I explicitly define product by individual accounts (but then product 3 aggregated without possibility to allocate to its subcategories). For volumes table, I already have this information.

I would like to allocate costs and revenues of product 3 to its subcategories using their share in volume sales. 

I would really appreciate any help. The sample data and file could be found through the following link:

https://drive.google.com/file/d/1Y83RoOduz0n_NqyOSDYOB9DQ4GUOOT9g/view?usp=sharing  

[v-xiaotang · Accepted solution]

Hi @Reroo 

Thanks for reaching out to us.

>> I would like to allocate costs and revenues of product 3 to its subcategories using their share in volume sales. 

it seems that is impossible.. In your model, to calculate sales per subcategory, your calculation path looks like this (see below),

What we can get is the sale of each product (see picture below), not the sale of each subproduct. this is because there is no subproduct in your Account table.

If you want to calculate the sale of a subproduct via the second path(see below), you need to ensure that there are no duplicates between the dates of each subproduct.

If this answer helps, please consider accept it as the solution, thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Reroo]

Hi,

this is also the difficulty I have been facing. In terms of my data model I quite open to rearrange if it is necessary to get the calculation I need. I need here some tipps on how this calculation could be permorfed best.

My goal is to get this dynamic since I will have a slicer for product in detail (product 3 with its subcategories) so that a user could see the development of different margnis by product.

[v-xiaotang]

Hi @Reroo 

Thanks for your reply.

>> In terms of my data model I quite open to rearrange if it is necessary to get the calculation I need.

Yes, between the Bookings and DimProductDetail tables, you need to establish a relationship, for example, you can add a subcategory column in 'Accounts', to establish a corresponding relationship between 'DimProductDetail' and 'Accounts'. Then we can calculate sales per subcategory.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.