Hi Exports,
Im getting sum of subtotal wrong in pivot table FC AVG COGS column, here im getting total -0.14 its wrong count its suppose to be -64.86 -right one, Please find the below pivot table for 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)
can you any one help to resolve this issue.
