Forum Discussion
Cost calculation from Bottomup
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.
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.
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.
Hello so SUMX(Table;[Cost of Month]*[Volume of Month]) should give you your expected result.
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.