How to use ROUNDDOWN() in pivot table's calculated field and return correct grand totals?-CodePudding

Without ROUNDDOWN() (=(Cheating*25)-('Not Cheating'*100)):

I already have this formula outside of the pivot table as a temporary solution:

=ARRAYFORMULA(IF(J2:J <> "", ROUNDDOWN((K2:K*25)-(L2:L*100), -3), ""))

In the pivot table, calculated field put

=ArrayFormula(IF(LEN((Cheating*25)-('Not Cheating'*100))>4,ROUNDDOWN((Cheating*25)-('Not Cheating'*100),-3),(Cheating*25)-('Not Cheating'*100)))

Note:
Keep in mind ...>4... in the formula decides when to rounddoun if the LEN length of the value is greater than >4 then rounddoun.

I hope this is helpful.

CodePudding user response：

Short Answer: Not Possible

How to use ROUNDDOWN() in pivot table's calculated field and return correct grand totals?

I don't think this is possible because what the pivot table is doing in the grand total row, is applying the same calculated formula to the individual grand totals in columns K and L. It does not actually calculate the total of your calculated field column.

Workaround

It's possible to get the pivot table to show the values you want, by adding a helper column to the spreadsheet:

Insert a column I after column H of your spreadsheet. In I1 put the following formula:

I1:

={"Weight";ARRAYFORMULA(ROUNDDOWN(COUNTIFS(E$2:E,E2:E,G$2:G,1)*25-COUNTIFS(E$2:E,E2:E,H$2:H,1)*100,-3)/COUNTIFS(E$2:E,E2:E,G$2:G H$2:H>0,TRUE))}

Then, add that column as a value in your pivot table, using SUM as the summary statistic.

Formula explanation

This part of the formula

ROUNDDOWN(COUNTIFS(E$2:E,E2:E,G$2:G,1)*25-COUNTIFS(E$2:E,E2:E,H$2:H,1)*100,-3)

calculates the rounded aggregate sum for each identifier, and places it in every row where that identifier is found.

Then /COUNTIFS(E$2:E,E2:E,G$2:G H$2:H>0,TRUE) divides (averages) that value among all of the rows for that identifier, which allows the SUM in the pivot table to aggregate them properly.

The main idea is that we are calculating the ROUNDDOWN function outside the pivot table, so that the pivot table only views the already-rounded values.

I'm not sure this will give you correct totals for individual month-years, but you should be able to adapt it to do so with an additional condition on the COUNTIFS.