Calculations with multiple constraints

[Anonymous]

Hello,

I'm a uni student doing a final project for my IS degree and have only been using PowerBI for a couple of months.

I'd really appreciate some help to solve the following (Model attached).

I need to be able to calculate product weighted average cost, where most of the tables in the model are separated.

Some items in Product[STOCKCODE] have a list of ingridients they consist of.
Those ingridients are all grouped under the same HDR[BILLCODE],  but different indiividual LINES[STOCKCODE].
Most of the Ingridients in LINES[STOCKCODE] use Standard costs, but some costs change based on month and year.
The items with dynamic price are in WAC Cost Table.

Because there are so many conditions I'm getting lost whether tu use calculated  column or measures, as both have their own limitations. Or maybe i'm too greedy trying to solve all at once and i need to take smaller steps.
Hope that's not too complicated.

Tables connections:
Product[STOCKCODE]=HDR[OUTPUTCODE],
HDR[BILLCODE] = LINES[BILLCODE],
LINES[STOCKCODE] = WAC SUPP[BOM Stock Code],
WAC SUPP[BOM Stock Code]=WAC Cost Table [BOM Stock Code]

Any guidelines are higly appreciated. 

[az38]

Hi @Anonymous 

let's make a first small step.

could you share an example of your dataset (for example for one product)?

then based on this example try to describe what do you want to get as output and how exactly it should be calculated

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks for getting back to me! Here is the sample data.


NOTE: Output_code=Stockcode and Description=Description_2 are same columnsfrom Product table. Names changed as 1 code&Description belongs to the Finished Product (HVT1KG), while the 2nd one belongs to the product components (Labour, Cheese etc)

1. HDR is just a table where Finished Product is assigned a Billcode, so its easier to define the Finished Product components, without referenceing the same Product table in the process.

2. Finished Product.Standardcost is calculated by multiplying each ingridient's  Product.Standardcost by LINES.Quantity

3. The snapshot is basically the LINES table (Without Stdcost and WAC)

4. WAC is only applicable to a handfull of Products, the rest uses standard. (i.e for  HVT1KG all components cost to be calculated based on standard, EXCEPT For CHSRR, the cost of which will depend n the date pf the transsaction, as WAAC changes monthly.

4. WAC SUPP is a table to avoide many to many relationship between LINES and WAC, as neither have unique values:(one LINES.Stockcode can be a component in many finished products and one WACCostTable.BOMStockCode appears many times in the table as Wac cost changes monthly.

Requirement.

We have Sales fact table, which is connected to Product table.

IF Finished Sold Product has a component, which is listed in the WAC table, use the cost from WAC table, instead of Standard cost.
WAC will depend on the Product Stockcode and the date of the transacation. 

I hope this makes it clearer.
Thanks for your time.

[az38]

Hi @Anonymous 

it's more clear but its still dificcult to make a full solution

maybe you should try to use ISINSCOPE function https://docs.microsoft.com/en-us/dax/isinscope-function-dax

smth like

Measure = SWITCH( TRUE(),
ISINSCOPE('WAC SUPP'[BOM Stock Code]),
SUM('WAC Cost Table'[WAC]),
SUM('Product'[StandartCost])
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks for your response.

The solution sums all WAC values for a particular Product, however the fact also depends on the date of the transaction.
Hence Cost at WAC will depend on
1. ProductID (if its inscope of the BOM Stockcodes)
2. Date of transaction (If its within certain Month and Year in WAC Table (Invoice date inscope of WAC Cost table MonthYear)