Powerpivot Subtotals not adding up

[JEB85]

Hi,

My base data is set up as follows:

Site	Product	KG	Variable Contribution	Period	Year
A	1​	1,949​	5,367​	Prior Period	Current Year
A	2​	2,285​	6,260​	Prior Period	Current Year
A	2​	1,142​	2,952​	Current Period	Current Year
A	3​	2,281​	3,258​	Prior Period	Current Year
A	3​	919​	1,056​	Current Period	Current Year

This has been loaded as a data table [Table 1] in to powerpivot.

My powerpivot, with measures, looks likes so:

		Values
Site	Product	CP KG	PP KG	Var in KG	PP VC	PP VC/KG	Volume Impact
A	1​		1,948.80​	-1,948.80​	5,366.53​	2.75​	-5,366.53​
	2​	1,142.40​	2,284.80​	-1,142.40​	6,260.05​	2.74​	-3,130.02​
	3​	918.72​	2,280.96​	-1,362.24​	3,258.46​	1.43​	-1,946.02​
A Total		2,061.12​	6,514.56​	-4,453.44​	14,885.03​	2.28​	-10,175.61​
Grand Total		2,061.12​	6,514.56​	-4,453.44​	14,885.03​	2.28​	-10,175.61​

The measures are as follows:
CP KG = CALCULATE(SUM([KG]),Table1[Period]="Current period",Table1[Year]="Current Year")
PP KG = CALCULATE(SUM([KG]),Table1[Period]="Prior period",Table1[Year]="Current Year")
Var in KG =Table1[CP KG]-Table1[PP KG]
PP VC =CALCULATE(SUM(Table1[Variable Contribution]),Table1[Period]="Prior Period",Table1[Year]="Current Year")
PP VC/KG =Table1[PP VC]/Table1[PP KG]
Volume Impact = Table1[Var in KG]*Table1[PP VC/KG]

Everything is working as i'd expect except for the 'Volume Impact' totals which i'd expect to be -10,442.57 (the sum of -5,366.53,-3,130.02,-1,946.02) yet it's returning -10,175.61.

Can anyone tell me what i'm doing wrong in the powerpivot calc?

Thanks

 

[jasonb75]

Try increasing the number of decimal places shown. I would expect that the figures being calculated are of a higher precision than those visible.

 

[JEB85]

Try increasing the number of decimal places shown. I would expect that the figures being calculated are of a higher precision than those visible.

Hi,

The variance is too big to be rounding / /driven from decimal places.

This is the base data with 4 decimal places:

Site	Product	KG	Variable Contribution	Period	Year
A	1​	1,948.8000​	5,366.5283​	Prior Period	Current Year
A	2​	2,284.8000​	6,260.0452​	Prior Period	Current Year
A	2​	1,142.4000​	2,952.0926​	Current Period	Current Year
A	3​	2,280.9600​	3,258.4572​	Prior Period	Current Year
A	3​	918.7200​	1,055.8990​	Current Period	Current Year

 

[jasonb75]

The variance is too big to be rounding / /driven from decimal places.

I disagree. Rounded to 4 decimal places the results that you were getting are correct. This is done using worksheet formulas on the same principle so that you can see where the result is coming from.

Book1
	A	B	C	D	E	F	G	H
10	Site	Product	CP KG	PP KG	Var in KG	PP VC	PP VC/KG	Volume Impact
11	A	1		1,948.80	-1,948.80	5,366.53	2.75376	-5,366.53
12		2	1,142.40	2,284.80	-1,142.40	6,260.05	2.739866	-3,130.02
13		3	918.72	2,280.96	-1,362.24	3,258.46	1.428546	-1,946.02
14	A Total		2,061.12	6,514.56	-4,453.44	14,885.03	2.284887	-10,175.61
Sheet2

Cell Formulas
Range	Formula
G11:G14	G11	=F11/D11
H11:H14	H11	=E11*G11
E11:E14	E11	=C11-D11
F14	F14	=SUM(F11:F13)

 

[JEB85]

Thanks, but i don't want the site total to calculate as an average, i'd like it to be the sum of it's parts.
So if i were doing it in regular excel, where your formula in H14 = E14 * G14, it sould be =sum(H11:H13) =-10,442.57. Is it possible to do this in powerpivot?

 

[jasonb75]

As far as I know the total of a calculated column will always use the same formula as the rest of the column in the same way that I've shown above. I'll look into it but I don't believe that it can be changed.

 

[JEB85]

O

As far as I know the total of a calculated column will always use the same formula as the rest of the column in the same way that I've shown above. I'll look into it but I don't believe that it can be changed.

Ok, thanks for your help.

 

[danzon]

You can make the Subtotals calculate differently than the individual rows...

Here is an example of a measure I have .. we assign points to each BRAND based on sales amounts...
so what it does is it says..

BrandLevelPoints:=IF(COUNTROWS(VALUES(tGroups[FOCUS]))=1,[OctPts],IF([OctChg]>0,SUMX(VALUES(tGroups[FOCUS]),[OctPts]),BLANK()))


if it is a single brand row then use my [OctPts] calculation...
if it is NOT a single Brand row.. like with a Subtotal or Grand Total .. then SUM the results of all the OctPts calculations that are part of that subtotal

 

[danzon]

looking at your data ..it might be as simple as

Measure1:=SUMX(VALUES(Table1[Product]),[Volume Impact])

 

[JEB85]

Danzon - thanks a lot for your solution, it works!