Im getting sum of subtotal wrong in pivot table FC AVG COGScolumn, here im getting total -0.14 its wrong count its suppose to be -64.86 -right one, Please find the below pivot tablefor your ref.
BMC Market
Posting Period
CY AVG Sales to thirds
FC AVG Sales to thirds
CY AVG COGS
FC AVG COGS
numerator
denominator
num/den
DACH
1
27.09
27,092.32
13.22
-13.22
-20,324.96
1,536.90
-13.22464081
DACH
2
28.03
28,034.52
13.71
-13.71
-20,133.71
1,468.67
-13.70881123
DACH
3
28.46
28,458.52
12.77
-12.77
-27,253.17
2,133.52
-12.77382571
DACH
4
25.67
25,671.16
12.04
-12.04
-19,274.00
1,600.98
-12.03890858
DACH
5
27.99
27,988.64
12.96
-12.96
-20,750.48
1,600.51
-12.96494983
DACH
6
25.28
42.28
11.97
-0.02
-2,411.64
126,276.00
-0.01909818
DACH
7
21.52
42.94
9.84
-0.02
-1,880.74
92,365.00
-0.02036208
DACH
8
26.99
44.21
12.52
-0.02
-2,312.70
112,326.00
-0.020589193
DACH
9
-
44.75
-
-0.02
-2,783.65
134,661.00
-0.020671519
DACH
10
-
52.06
-
-0.02
-3,095.36
127,397.00
-0.024296936
DACH
11
-
57.37
-
-0.03
-3,897.66
149,098.00
-0.02614158
DACH
12
-
48.05
-
-0.02
-3,350.60
151,396.00
-0.02213136
DACH
Total
26.10
26,098.18
12.21
-0.14
-127,468.68
901,859.57
-0.141339827
Total
26.10
26,098.18
12.21
-0.14
-127,468.68
901,859.57
-127,468.68/901,859.57=-64.86 right answer
-0.14 now - error value
expression:
(Sum({<[FISCPER]={'>=$(VMinFCPeriod)'}>}[Cost of Sales FC]) + Sum({<[FISCPER]={'<$(VMinFCPeriod)'}>}[Cost of sales.CS_TRN_GC]/1000))/
(sum({<[FISCPER]={'>=$(VMinFCPeriod)'}>}[Quantity])+sum({<[FISCPER]={'<$(VMinFCPeriod)'}>}[Sales to thirds./BIC/NQUANTITY_ORIGIN]/1000))
the above expression im using to generate FC AVG COGS values
along with for your reference i have created numerator denominator fields you can do the division with numerator/denominator(-127,468.68/901,859.57=-64.86 right answer)
