Cost calculation from Bottomup

[rajibmahmud]

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 would be Month 1 vol * month 1 cost + Month 2 vol * month 2 cost .... this way.

And the Brand cost would be sum of this from bottom up. However when ever I tried to do it using calculate, the Full year number is Cost of 1 + Cost of 2 ... * total volume, which is incorrect. I am attaching PBI file, can someone guide me on this.

Below is the link

https://1drv.ms/f/s!AuSkszFGzDQzjSL9CxVCzI8VXYGN

[Floriankx]

If you have vol. and cost column in your table use Sumx

Total_Cost:=(Table,[vol]*[cost]). This should end up as supposed.

Due to company restrictions I am not able to check out your provided link.

So maybe just some information of your data structure would be fine.

Best regards.

[rajibmahmud]

@Floriankx

The source data's are in 2 different table. I couldnt connect those using Sumx for some reason, although the tables are connected via relation.

Data would look like below:

YearMonthChannelSKUValueMeasure

2017	1	Rural	AAA	57	Volume
2017	2	Rural	AAA	51	Volume
2017	3	Rural	AAA	56	Volume
2017	4	Rural	AAA	39	Volume
2017	5	Rural	AAA	51	Volume
2017	6	Rural	AAA	51	Volume
2017	7	Rural	AAA	57	Volume
2017	8	Rural	AAA	53	Volume
2017	9	Rural	AAA	46	Volume
2017	10	Rural	AAA	46	Volume
2017	11	Rural	AAA	56	Volume
2017	12	Rural	AAA	38	Volume

YearMonthSKUSCC.Ton

2017	1	AAA	11
2017	2	AAA	13
2017	3	AAA	14
2017	4	AAA	15
2017	5	AAA	10
2017	6	AAA	14
2017	7	AAA	17
2017	8	AAA	18
2017	9	AAA	11
2017	10	AAA	15
2017	11	AAA	18
2017	12	AAA	10
2017	1	AAB	17
2017	2	AAB	18
2017	3	AAB	16
2017	4	AAB	16
2017	5	AAB	14
2017	6	AAB	17
2017	7	AAB	16
2017	8	AAB	14
2017	9	AAB	17
2017	10	AAB	18
2017	11	AAB	18
2017	12	AAB	16

[Floriankx]

Can you add headings please.

How did you relate the tables?

[rajibmahmud]

When I pasted table, the header disappeared for some reason. Pasted again..

Year             Month      SKU         SCC.Ton

2017	1	AAA	11
2017	2	AAA	13
2017	3	AAA	14
2017	4	AAA	15
2017	5	AAA	10
2017	6	AAA	14
2017	7	AAA	17
2017	8	AAA	18
2017	9	AAA	11
2017	10	AAA	15
2017	11	AAA	18
2017	12	AAA	10
2017	1	AAB	17
2017	2	AAB	18
2017	3	AAB	16
2017	4	AAB	16
2017	5	AAB	14
2017	6	AAB	17
2017	7	AAB	16
2017	8	AAB	14
2017	9	AAB	17
2017	10	AAB	18
2017	11	AAB	18
2017	12	AAB	16

Year             Month     Channel     SKU          Value       Measure

2017	1	Rural	AAA	57	Volume
2017	2	Rural	AAA	51	Volume
2017	3	Rural	AAA	56	Volume
2017	4	Rural	AAA	39	Volume
2017	5	Rural	AAA	51	Volume
2017	6	Rural	AAA	51	Volume
2017	7	Rural	AAA	57	Volume
2017	8	Rural	AAA	53	Volume
2017	9	Rural	AAA	46	Volume
2017	10	Rural	AAA	46	Volume
2017	11	Rural	AAA	56	Volume
2017	12	Rural	AAA	38	Volume
2017	1	Rural	AAB	49	Volume
2017	2	Rural	AAB	31	Volume
2017	3	Rural	AAB	41	Volume
2017	4	Rural	AAB	52	Volume
2017	5	Rural	AAB	35	Volume
2017	6	Rural	AAB	37	Volume
2017	7	Rural	AAB	57	Volume
2017	8	Rural	AAB	59	Volume
2017	9	Rural	AAB	58	Volume
2017	10	Rural	AAB	54	Volume
2017	11	Rural	AAB	49	Volume
2017	12	Rural	AAB	45	Volume

U created distinct table for SKU/Product, Month , Year etc to related the tables.

[Floriankx]

And what is your expected result?

At the moment it seems you have one value per product and month so every value is unique.

In this case, Sum([Value] should be fine.

But maybe i misunderstood something.

[rajibmahmud]

It would be easier if you could open the excels and PBI files.

So what is happening is, when I am looking at the cost at month level the number is ok. System is pulling sales Quantity at monthly level and multiplying the monthly cost from another table which is linked.

However when I am looking at the sum or looking at data at quarter level using drilldown option, PBI is not calculating the cost from bottom up, rather its summing the cost and doing the calculation.

Below example will clarify.

Cost of Jan is 10 * Vol of Jan is 20 = 200 

Cost of Feb is 11 * Vol 30 = 330

Cost of Mar is 10 * Vol 30 = 300

Hence the Quarter cost should be = 200+330+300 = 830

But when I am using drilldown the result is = (10+11+10) * (20+30+30) =  2480.

The cost is getting calculated incorrectly.

[Floriankx]

Hello so SUMX(Table;[Cost of Month]*[Volume of Month]) should give you your expected result.

[rajibmahmud]

Hi, Cost of month is in seperate table. Hence I cant use Sumx.

Than again, I could use Sumx in combination with Related. But I cant use it, as the tables are connected via an intermediate table, as both table have repeated Product code/name due to multiple Year, Month.

[Floriankx]

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]

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

[Floriankx]

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.

[rajibmahmud]

Thanks a lot. I will put it in my model and try.

But it seems in Filter I have to put Year hard coded rather than it being dynamic. Am I right?

[Floriankx]

Hello,

no it is not hard coded. With VAR you define your actuals dynamically.