Forum Discussion

rajibmahmud's avatar
rajibmahmud
Helper III
8 years ago

Cost calculation from Bottomup

Hi Team,   I am trying to calculate a cost using bottom up method at monthly level.   Where cost would be Vol sold in Month 1, multiplied by Cost in month 1 at Product Level. The total year cost ...
Need Help
Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello is the combination of Month, Year and SKU unique in each table?

 

If it would be you could use the following:

Measure:=SUMX(fCost;
VAR Actual_Year=fCost[Year]
VAR Actual_Month=fCost[Month]
VAR Actual_SKU=fCost[SKU] RETURN
fCost[Cost]*CALCULATE(SUM(fValue[Value]);FILTER(fValue;fValue[Year]=Actual_Year&&fValue[Month]=Actual_Month&&fValue[SKU]=Actual_SKU)))

Assumptions made:

Table with Cost is named fCost

Table with Volume is named fValue. Volume is here called Value.

Measure is created in fCost table.

The combination of Year, Month and SKU is unique in each row of fCost.

Now combination (Year, Month, SKU) appears in fValue which is not in fCost.

Tables are not related.

 

If you create a Pivot with Year, Month and SKU from fCost it should be fine.

rajibmahmud's avatar
rajibmahmud
Helper III
8 years ago

It doesnt work. As in the tables Month and Year is not unique due to dataset nature.

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Could have been tables aggregated to Year, Month and SKU.

     

    In this case it probably would be best to create a DateTable (dDate) with distinct Year and Month Values.

    Additionally I would create a distinct table for SKU (dSKU).

    The tables shouldn't be related.

    The I added in dDate the following Measure:

    Total Cost:=SUMX(dSKU;
var Actual_SKU=dSKU[SKU]
var Date_Value=SUMX(dDate;
	var Actual_Year=dDate[Year]
	var Actual_Month=dDate[Month Number]
	var Cost_per_month=CALCULATE(SUM(fCost[SCC.Ton]);FILTER(fCost;fCost[Year]=Actual_Year&&fCost[Month]=Actual_Month&&fCost[SKU]=Actual_SKU))
	var Value_per_month=CALCULATE(SUM(fValue[Value]);FILTER(fValue;fValue[Year]=Actual_Year&&fValue[Month]=Actual_Month&&fValue[SKU]=Actual_SKU))
	Return Cost_per_month*Value_per_month)
Return Date_Value)

    Please check it out.

     

    There would also be the possibilitie of creating dates out of Month and Year and relate them to your date table.