Relationship on field that has aggregation value

[victor_erathos]

I need to relate a dataset of target sales and one of executed sales, with the important detail that the sales can be done on two different channels (ON TRADE and OFF TRADE). I've reduced the data to something simpler that still involves the problem. The following is an example of the executed sales:

The target values for these sales can be assigned to the same ON TRADE and OFF TRADE channels, but some targets can be achieved in any channel, so the data I receive for targets also inclues a ANY value on the channel field:

The baseline of how the tables need to be related lies on the product id, and at first it is as follows (I'm ignoring the date relation for the moment just to focus on the problem):

My problem lies on the fact that I can't find a way to easily relate both tables with regard to the channel, since the target table has a value for channel that should be attributed to both channels. I want to build a full table such as the following (e.g. for the month of January), which shows the whole picture of all products, channels and how much has been achieved of the respective targets. 

However, these are naturally incorrect, since it is not considering the different channels (product_id 1 has its values duplicated for both ON and OFF TRADE channels). The result for January should actually look like the following:

What is the best way to approach this problem? Here is also a link to the .pbix file I built to exemplify the problem.

Thank you in advance for the help!

[DataInsights · Accepted solution]

@victor_erathos,

This solution uses a calculated column in the Target table which summarizes the appropriate amounts in the Sales table. I attempted to solve this via data modeling with a Channel mapping table and an intermediary DistinctChannel table for relationships, but the result wasn't correct for product_id 4 (ON TRADE and OFF TRADE from Sales weren't grouped into the ANY row).

Calculated column in Target table:

actual_sales = 
VAR vYear = YEAR ( Target[month] )
VAR vMonth = MONTH ( Target[month] )
VAR vProduct = Target[product_id]
VAR vChannel = Target[channel]
VAR vTable =
    FILTER (
        Sales,
        YEAR ( Sales[date] ) = vYear
            && MONTH ( Sales[date] ) = vMonth
            && Sales[product_id] = vProduct
            && Sales[channel] = vChannel
    )
VAR vTableAny =
    FILTER (
        Sales,
        YEAR ( Sales[date] ) = vYear
            && MONTH ( Sales[date] ) = vMonth
            && Sales[product_id] = vProduct
    )
VAR vResult =
    SWITCH (
        vChannel,
        "ANY", SUMX ( vTableAny, Sales[value] ),
        SUMX ( vTable, Sales[value] )
    )
RETURN
    vResult

Measures:

Target Sales = SUM ( Target[target_sales] )

Actual Sales = SUM ( Target[actual_sales] )

Achieved = DIVIDE ( [Actual Sales], [Target Sales] )

In the visual, use Product[product_id] and Target[channel], along with the measures above:

[victor_erathos]

@DataInsights that's it! Thank you for the help and clear solution!