Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculations with multiple constraints

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. 

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Community Champion
        6 years ago

        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