Hi,
I've taken the file from your lesson 6.07 Ideal data structure ( please see attachment) and added some data: a quantity column in the actual and the budget fact table. I've also created a third fact table called "FactCosting" which gives the cost of the Department per ton.
I've created some new variables called Actuals/t, Budget/t, Cost/t, and then ActCost which multiplies the Cost/t with the quantites from the FactActuals table. However in the pivot table on the sheet "Pivot2" the ActCost do not sum up correctly; The "Americas Total" is not Equal to the sum above. Do you know why? Is there anything I can do with my formulas to display this correctly. Thanks in advance for any help
Hi Caspar,
It looks like calculation is for Total America row (Cost/T X Quantity) like below.
Total America quantity is 4,835.082625 and average cost/t for whole America is 25 (in column D). So 25 X 4,835.082625 = 120,399.
Kind Regards
Arun
Hi Caspar,
You hit the average of averages problem, see this for a description and a solution: https://community.powerbi.com/t5/Desktop/change-aggregation-of-grand-total-in-pivot-table-reconcile-average-total/td-p/233365
https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
Hi Catalin, Thanks for your answer. I'll have a look into the articles you forwarded.
