Grand Total is incorrectly displayed with Calculated Field in pivot table

Question

I tried using a calculated field for a pivot table in Google Sheets. For example, there are two pivot table columns, Column1 and Column2, containing numbers.

In the calculated field, I write for example =Column1+Column2. In my case, this is the exact formula:

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

The result is correctly displayed. However, when it comes down to the grand totals, it will consider those values as standard values and calculate them the same way instead of displaying the grand totals for the above cells.

The current solution I use is a column outside of the pivot table and just calculate with ARRAYFORMULA. This way, I can tell the formula that it will only calculate for the columns that have values in a third column, so the grand totals are not calculated the same way.

Is there a way I can either use calculated field with proper grand totals (this is what I would prefer), or the ARRAYFORMULA with grand totals at the bottom of the formula calculation?

Here is a spreadsheet with two examples

Answer 1

The Pivot Table values have no impact on the custom formula in the Pivot Table report. It instead uses values from the original data. Thus, the source data, not the data from the pivot table, should be the basis for the calculation.

Even if the source's grand total is used, it is still handled as a regular value, and it had better be disabled.

You can try this approach until you share a minimal example.

ArrayFormula(IF(A3:A="Grand Total",,[The rest of the formula]))

Update

You are using =ROUNDDOWN Like this

=ROUNDDOWN((A3*25)-(B3*100), -3)

When setting the places to -3 it will round down the whole part "the numbers on the left of the point." 3 places to the left will be 000 see this example.

The input is 826.646

Output   Formula
826     =ROUNDDOWN(826.646, 0)  
826     =ROUNDDOWN(826.646) 
826.6   =ROUNDDOWN(826.646, 1)  
826.64  =ROUNDDOWN(826.646, 2)  
820     =ROUNDDOWN(826.646, -1) 
800     =ROUNDDOWN(826.646, -2) 

The sloution

Try

=ROUNDDOWN((Cheating*25)-('Not Cheating'*100), 0)