-2

So, I made a pivot table with calculated formula in google sheets but somehow, the subtotal for each client was shown wrong

the code for Total Income I've been using is this one;

=IF((business_initiated+user_initiated)>=1000;($C$4*business_initiated+$D$4*user_initiated);0)

the code for Total Invoice I've been using is this one;

=IF((business_initiated+user_initiated)>=1000;IF(($C$5*business_initiated+$D$5*user_initiated)<0;0;($C$5*business_initiated+$D$5*user_initiated));500000)

client month Total Business Initiated Total IDR Total User Initiated Total IDR Total Message Total Income Total Invoice
A 1 3.880 Rp1.437.462 668 Rp0 4.548 Rp454.800 Rp1.805.397
A 2 3.416 Rp1.265.560 550 Rp0 3.966 Rp396.600 Rp1.568.500
A 3 6.007 Rp2.225.473 699 Rp0 6.706 Rp670.600 Rp2.610.483
A 4 4.473 Rp1.657.157 645 Rp0 5.118 Rp511.800 Rp2.012.423
A 5 15.292 Rp5.665.380 2.380 Rp1.310.904 17.672 Rp1.767.200 Rp6.976.284
A 6 15.898 Rp5.889.891 914 Rp0 16.812 Rp1.681.200 Rp6.393.322
Total 48.966 Rp18.140.924 5.856 Rp3.225.485 54.822 Rp5.482.200 Rp21.366.408
B 5 0 Rp0 569 Rp0 569 Rp0 Rp500.000
B 6 1.749 Rp647.970 1.400 Rp771.120 3.149 Rp314.900 Rp1.419.090
Total 1.749 Rp647.970 1.969 Rp1.084.525 3.718 Rp371.800 Rp1.732.495
C 1 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 2 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 3 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 4 0 Rp0 1 Rp0 1 Rp0 Rp500.000
Total 0 Rp0 4 Rp0 4 Rp0 Rp500.000

Here are the expected results;

client month Total Business Initiated Total IDR Total User Initiated Total IDR Total Message Total Income Total Invoice
A 1 3.880 Rp1.437.462 668 Rp0 4.548 Rp454.800 Rp1.805.397
A 2 3.416 Rp1.265.560 550 Rp0 3.966 Rp396.600 Rp1.568.500
A 3 6.007 Rp2.225.473 699 Rp0 6.706 Rp670.600 Rp2.610.483
A 4 4.473 Rp1.657.157 645 Rp0 5.118 Rp511.800 Rp2.012.423
A 5 15.292 Rp5.665.380 2.380 Rp1.310.904 17.672 Rp1.767.200 Rp6.976.284
A 6 15.898 Rp5.889.891 914 Rp0 16.812 Rp1.681.200 Rp6.393.322
Total 48.966 Rp18.140.924 5.856 Rp3.225.485 54.822 Rp5.482.200 Rp21.366.408
B 5 0 Rp0 569 Rp0 569 Rp0 Rp500.000
B 6 1.749 Rp647.970 1.400 Rp771.120 3.149 Rp314.900 Rp1.419.090
Total 1.749 Rp647.970 1.969 Rp1.084.525 3.718 Rp371.800 Rp1.919.090
C 1 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 2 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 3 0 Rp0 1 Rp0 1 Rp0 Rp500.000
C 4 0 Rp0 1 Rp0 1 Rp0 Rp500.000
Total 0 Rp0 4 Rp0 4 Rp0 Rp2000.000

So, basically if the total message for each month is less than 1000. The income income will be Rp0 and Total Invoice will be Rp500000.

But somehow the sub-total wasn't displayed correctly if the Total Income is 0 and Total Invoice is Rp500000.

Does anyone had idea how to solve this problem? Thank you so much.

Edit (Solved): I believe we couldn't do a more complex formula inside the Pivot Table calculated field. So, instead of using complex formula inside the Pivot Table, applied it outside the pivot then work around the problem.

sadil
  • 19
  • 5
  • Share a copy of your sheet. Probably have issue with display format. – idfurw Aug 18 '21 at 15:12
  • I'm sorry but I couldn't share the sheet, since it's private data. Can you explain more about the display format? – sadil Aug 18 '21 at 15:25
  • If you cannot share a copy as is, please anonymize it and / or make at least one minimum and reproducible example. I agree with isfurw. – Mike Steelson Aug 18 '21 at 15:33
  • I think we can identify the problem with 2 rows. Could you do a flavor? 'Display format' means your value may not be formatted as number but string, so it is regarded as `0` upon calculations. – idfurw Aug 18 '21 at 15:42
  • https://docs.google.com/spreadsheets/d/15he5w1X7uyfKNkvW9hLx-iZnrm7OXa-z9dPfPznk2Ok/edit?usp=sharing i've tried to reproduce the sheet. you can check it if you can, but I can't show the original data to the sheet – sadil Aug 18 '21 at 16:04
  • Please make your reproduced sheet accessible for people to check and see how we can help. – Jason E. Aug 18 '21 at 16:41
  • Already done that – sadil Aug 18 '21 at 17:36
  • sheet is private – player0 Aug 18 '21 at 19:50

1 Answers1

1

Nevermind. I've created a new column for each income and invoice, respectively. So, in the end, I could use the same principle for the formula. After some digging through, I believe there's something different on the Pivot Table Calculated Field, as you can't do much with the calculation formula.

sadil
  • 19
  • 5