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

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's avatar
rajibmahmud
Helper III
8 years ago

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's avatar
    Floriankx
    Solution Sage
    8 years ago

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

    • rajibmahmud's avatar
      rajibmahmud
      Helper III
      8 years ago

      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'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.