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

In this post, I got to know that using ROUNDDOWN() in a pivot table's calculated field is what causes the grand total to display an incorrect value. So I want to ask, how to use ROUNDDOWN() in a pivot table's calculated field and still return correct grand totals? Specifically, how to use the one that rounds down to the nearest thousand (, -3)? How it is now (showing 0 when it is not near a thousand) is perfect, the only issue is how to show the grand total correctly with those zeroes instead of irrelevant non-thousand values

With ROUNDDOWN() (=ROUNDDOWN((Cheating*25)-('Not Cheating'*100), -3)):

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), ""))

Here is the spreadsheet with the source data.

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:

1. 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))}

2. 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.