Pivot table macro not applying decimal place setting

Green_ Desmond
Contributor
May 20, 2024

I'm using a pivot table and want to simply round the data from a calculated column, to the nearest integer, no decimal places.

Here is the configuration of the pivot table.

Pivot table not rounding.JPG

Pivot table not rounding 2.JPG

It doesn't seem to matter what I enter into the 'Decimal places' setting, the output is at 1 decimal place.

Am I missing something obvious or else how do I work around this?

1 answer

1 accepted

2 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 20, 2024

Hi @Green_ Desmond ,

The decimal places are applied to the calculated results. Here your calculated results are 6 and 7 (you get them using the Count operation type). So, the decimal places may be applied only to these numbers.

The per cents are counted automatically by the macro and are set to have one decimal place by default.

The use case for applying the number coming from the decimal places field may be when you have some column with numbers and you choose the Average operation type. Then you may get not integer results like the Count returns but some numbers as 5.4, 6.76 and so on. In this case when you set decimal places as 0, the results will be rounded.

So, you may leave everything as it is for simplicity or use the Table Transformer macro:

Mon 9-1.png

SELECT
"Count" AS 'Pass/Fail',
ROUND(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0))/(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))) * 100, 0) + "% " +
"(" + SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) + ")"
AS 'Pass Percent',
ROUND(SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))/(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))) * 100, 0) + "% " +
"(" + SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0)) + ")"
AS 'Fail Percent'
FROM T1

Mon 9-2.png

The result table was transposed (the last checkbox in the Settings tab) to match the outcome from your pivot table. But you may leave it as it is.

Also if you don't have empty cells in your table, you may count the total number of rows as COUNT('Pass/Fail') instead of the (SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0)) where we count the two statuses separately.

Hope this helps your case.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events