Calculate totals per variable costs

Power BI
1

Hi all,

Our company is in the F&B business where cost of many sold items changes frequently. Thus, we get to have -on a monthly basis- a different cost for every SKU sold (this is recorded on a table w/columns Month, Year, SKU, UnitCost imported in BI).

I need to calculate cost for sold products but I cannot get to ‘assign’ the right cost (year/month) to the each sales line. Sales table in BI consists of Date/SKU/Qty.

I would greatly appreciate all bright ideas :slight_smile:

2

Hi @Thimios,
It would be great if we can have a look at your model and the tables. But try this anyway :

  1. reate a key in both tables : Sales and UNIT COST to link both.
    Year&Month&SKU.

  2. Create a relationship between the two using that key.

  3. Add a calculated culumns that gets the unit cost from the UNIT COST table into Sales table (Using RELATED function).

  4. Then you can create a measure of your cost :

     `Total Cost = SUMX (Sales, Qty * Unit Cost )`
1 Like
3

Thank you @HASSAN_BI,

Sounds logical to create a common key. I’ll give it a try and let you know.

4

Hi @HASSAN_BI,

I could use your help on #3. How can this be done using the Food Cost table inactive realtionship to Sales table on the attached model?

Is Merge Queries something that I could use here? Are there any side effects with such a solution?

Sample Report v4.pbix (21.5 MB)

5

Hi @Thimios

Please check attached solution if it is helpful. If not, please let me know. I have created below measure to derive the cost.

Cost New = 
SUMX (
    sales,
    Sales[Quantity]
        *
        VAR salesdate = Sales[Date]
        VAR MaxCostDate =
            MAXX (
               CALCULATETABLE('Food Cost',all(dates), 'Food Cost'[Date] <= salesdate),
                'Food Cost'[Date]
            )
   VAR MinCostDate =
            CALCULATE(min('Food Cost'[Date]),all(dates))

Var FinalDate = if(isblank(MaxCostDate),MinCostDate,MaxCostDate)

        RETURN
            CALCULATE (
                max ( 'Food Cost'[Cost] ),
               all(dates), 'Food Cost'[Date] = FinalDate)
)
  1. Sumx - This will perform operation for each row of the Sales Table filtered by SKU Code in Visual. Here I am multiplying Quantity by Cost from “Food Cost” table. To derive cost I am doing below
  2. MaxCostDate -To identify Max Cost Date based on Sales Date. For example if Sales date for 1002 is 12th Dec 2019, it will get Max Cost Date by filtering Cost table below this date. In this case output will be 1st Dec 2019.
  3. MinCostDate - In case MaxDate is blank , it will get the minimum date available. For example, for 1002 there are sales for Nov 2019 but minimum date in Cost table is 1st Dec 2019.
  4. FinalDate is MaxCostDate. if not available then MinCostDate.
  5. Return will provide the cost value based on FinalDate.

EDNA_Solution_Sample Report v4.pbix (21.5 MB)

Thanks
Ankit J

2 Likes
6

Worked like a charm, thank you @ankit!
Great methodology to have in arsenal.