Forum Discussion
Cost calculation from Bottomup
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 |
Can you add headings please.
How did you relate the tables?
When I pasted table, the header disappeared for some reason. Pasted again..
Year Month SKU SCC.Ton2017 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.