Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.