DAX: Split cost from account by product -how to allocate cost between tables with diff. granularity?

[Anonymous]

Hello everyone! Could you please give some advice on the following issue?
I have two unrelated tables in my model. The first one contains cost by account level throught several years. The second one is at a lower level and contains the sales of products through those accounts over the same time period. I am trying to allocate the cost summarized in the first table to each of the products in the lower level detail table as follows:
  1. I've calculated a product share of business in the product sales table, which is the weight of the product sales within an account for the filtered period using the below formula.
   Formula: 

Product Share of Biz =

    Var Sales= SUM('Product Sales Table'[Sales])

    Var TotSales=CALCULATE(SUM('Product Sales Table'[Sales]),ALL('Product Sales Table'[Product]))

    Return

    DIVIDE(Sales,TotSales)
 2. I've calculated a Product Cost value by simply multiplying the share of business calculated above with the Cost value summarized from the other table, as below:

Product Cost = SUMX('Account Cost Table','Account Cost Table'[Cost]*'Product Sales Table'[Product Share of Biz])

Despite the Share of business formula being correct, the product cost result is wrong. As shown below, the formula simply returns the sum of the cost by account over the two years I've included in the table. The correct formula for Product Cost should return a value of 333 for Product 1, of Account 1, of Business Unit 1 during Fiscal Year 2023. Could you please advise on the issue?

ps: For some reason I cannot attached the tables and power bi file to the post. 

[Anonymous]

Link to the dummy files: https://drive.google.com/drive/folders/1CX4SQW_H33zdihYhKS1Hb_PJWg3NkS-s?usp=sharing

[Fowmy]

@Anonymous 

You can save your dummy PBI file  in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here. Ensure permission is granted.

[Anonymous]

Thank you! I am posting the link here: https://drive.google.com/drive/folders/1CX4SQW_H33zdihYhKS1Hb_PJWg3NkS-s?usp=sharing

[Fowmy]

@Anonymous 

I added a new column in the Sales table where the allocation of cost is calculated. I then used a measure to sum it.

Product Cost Allocated = 

VAR __Filters = 
    FILTER(
        'Account Cost Table',
        'Account Cost Table'[Fiscal Year] = 'Product Sales Table'[Fiscal Year] &&
        'Account Cost Table'[Quarter] = 'Product Sales Table'[Quarter] &&
        'Account Cost Table'[Business Unit] = 'Product Sales Table'[Business Unit] &&
        'Account Cost Table'[Account] = 'Product Sales Table'[Account]
    )
VAR __RecCount = 
        CALCULATE(
            COUNTROWS('Product Sales Table') , 
            REMOVEFILTERS('Product Sales Table'[Product])
        )
VAR __Result = 
    DIVIDE(
        CALCULATE(
            SUM('Account Cost Table'[Cost]) , 
            __Filters
        ),
        __RecCount
    )
RETURN
    __Result

[Anonymous]

Thanks for looking into it! I think it is a good step forward, however I've tested it with different data and noticed that using the count function to establish the number of product and then using that as a denominator is basically attributing an equal weight to the products that were sold. Therefore, If I change the sales of some products their share of business will stay the same which I want to avoid. Finally, the allocation of cost to each product should be determined by the weight of the product calculated based on the Sales column. I've used part of your code, but I don't have an optimal solution yet. 
I've removed the "RecCount" variable and am simply calculating a column with the product cost. Thanks for the tip with the filter context, this is a feature I haven't become familiar with in my couple of weeks experience with DAX and PBI. Next, I use this column to transfer it in a measure. Finally, I create a new column, "Product Cost Alloc_New"  where I simply multiply the "Product share of business" with the aforementioned measure of product cost. You can see from the below snapshot that despite the formula allocating each cost correctly according to the share of business, the total is inccorect. Probably because the SUMX context is repeating the product operation "[Product Share of Biz]*[PC as Msr]"  also on the total line. Do you have any ideas how to fix this? I've refreshed the files on the google drive with these changes.
Link: https://drive.google.com/drive/folders/1CX4SQW_H33zdihYhKS1Hb_PJWg3NkS-s?usp=sharing
The slightly changed column based on your code:

Product Cost_Col =

VAR __Filters =

    FILTER(

        'Account Cost Table',

        'Account Cost Table'[Fiscal Year] = 'Product Sales Table'[Fiscal Year] &&

        'Account Cost Table'[Quarter] = 'Product Sales Table'[Quarter] &&

        'Account Cost Table'[Business Unit] = 'Product Sales Table'[Business Unit] &&

        'Account Cost Table'[Account] = 'Product Sales Table'[Account]

    )

VAR __Result =

        CALCULATE(

            SUM('Account Cost Table'[Cost]) ,

            __Filters

        )

RETURN

    __Result

Snapshot of table with initial cost allocation and the new cost allocation:

[Fowmy · Accepted solution]

@Anonymous 

I modified the column that I created to consider  product sales as the weight.

Product Cost Allocated = 

VAR __Filters = 
    FILTER(
        'Account Cost Table',
        'Account Cost Table'[Fiscal Year] = 'Product Sales Table'[Fiscal Year] &&
        'Account Cost Table'[Quarter] = 'Product Sales Table'[Quarter] &&
        'Account Cost Table'[Business Unit] = 'Product Sales Table'[Business Unit] &&
        'Account Cost Table'[Account] = 'Product Sales Table'[Account]
    )
VAR __TotalSales = 
        CALCULATE(
           SUM('Product Sales Table'[Sales]) , 
           ALLEXCEPT( 'Product Sales Table' , 'Product Sales Table'[Fiscal Year] , 'Product Sales Table'[Quarter] , 'Product Sales Table'[Business Unit] ,'Product Sales Table'[Account])
        )
VAR __Ratio = DIVIDE( 'Product Sales Table'[Sales] , __TotalSales )
VAR __Result = 
        CALCULATE(
            SUM('Account Cost Table'[Cost]) , 
            __Filters
        ) * __Ratio
RETURN
    __Result

[Anonymous]

Awesome! Thanks a lot!