you can achieve this by creating a new column that calculates the allocated cost for each product. Assuming your table is named YourTableName, you can use the following DAX formula:
AllocatedCost =
VAR TotalCostWithoutProduct = CALCULATE(SUM(YourTableName[Cost]), FILTER(YourTableName, YourTableName[Product] = BLANK()))
RETURN
IF(
NOT(ISBLANK(YourTableName[Product])),
YourTableName[Cost],
YourTableName[Cost] * (YourTableName[Import] / TotalCostWithoutProduct)
)
This formula creates a new column called AllocatedCost. It calculates the total cost without a specified product for each UniqueDutyNumber using the TotalCostWithoutProduct variable. Then, for each row in the table, it checks if the product is specified. If it is, it simply uses the original cost. If not, it allocates the cost based on the proportion of the product's import value compared to the total cost without a specified product.
Please replace YourTableName with the actual name of your table.
Note: This formula assumes that the import value is used as the allocation factor. You may need to adjust it based on your specific allocation criteria.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
