Splitting cost by % Total of Revenue

[milkywaypowerbi]

I have a cost table which spilts cost by Product Line, Customer and Country and months as columns:

I have another table for revenue with greater level of details Product Line, Customer and Country but with additional Colour column by products.

I would like to allocate the product cost (table 1) by the product line country and customer based on % Total of revenue:

With the % total of revenue for each row and then multiply the cost of each month.

For instance:

Pen/America/Customer A total cost in Jan is 17 :

For the Revenue table the total revenue for all colours for Pen/America/Customer is 70:

Therefore the cost for Pen/America/A/Red = (37/70) * 17:

How do i do conduct this spilt in Powerbi?

You may find the excel here:

https://drive.google.com/drive/folders/1o8ScBiDRKbaC4WJT1Vf1djIddmXjcYEu?usp=sharing

[PaulDBrown]

First of all you need to unpivot the month columns in the cost table. 
Next create Dimension tables with unique values covering the range of values for each field for Product Line, country, Customer and Month. You the create 1-to-many relationships between each dimenaion field and the corresponding fields in each fact tables. 
Once you have done this, you can easily make calculations. Use the dimension fields in your visuals, measures, slicers, filters...
However, since the cost table doesn't provide details by colour, how are you distributing the cost by each colour?

[milkywaypowerbi]

HI @PaulDBrown ,

For the cost table which doesnt provide by color, it represents the cost of all colours in the category. I am facing some issue with this part as the cost table has one less level of details compared to the revenue table. So the 17 for Jan Pen/America/A is for both green and red. 

[PaulDBrown]

That's the thing. If you don't have costs per colour, and unless you decide on a % split by colour, you can only calculate the cost by colour by splitting the cost evenly.

In any case, since the cost allocation by colour is "arbitrary", it won't be a true reflection of the cost by colour (there is no real data to calculate it) and can therefore be misleading with regards insights/conclusions

[milkywaypowerbi]

Hi @PaulDBrown 

For the cost allocation wise, I would like to spilt among the colours by % total revenue by each category.

So it would be (Green/(Red +Green) for revenue  for Pen/ America/Customer A multiply by cost

[PaulDBrown · Accepted solution]

With This model:

Create the simple SUM measures for revenue and cost and then use this to calculate the cost by colour (the split is based on the revenue for each category by month/product/country/customer)

Cost by colour =
VAR Tot =
    CALCULATE (
        [Sum Revenue],
        ALLEXCEPT (
            Revenue,
            'Product Line'[DProduct Line],
            Country[DCountry],
            'Month'[DMonth],
            Customer[DCustomer]
        )
    )
RETURN
    DIVIDE ( [Sum Revenue], Tot ) * [Sum Costs]

To get

I've attached the sample PBIX file