Total sum is not ignoring the rows with zero values

Question & Answer
, , ,
1

Hey there.

In my pivot table I’m using four calculated field (Col 1, col 2, col 3 and col 4). The sum of col 1, col 2 is correct but the sum of col 3 and 4 is not. The sum of col 3 and 4 is not ignoring the rows with zero values, the real value is much lower than what is being shown. Here are the expressions for the calculated fields:
Col 1:
sum(valortotalprodutocomdesconto)

Col 2:
ifelse(
isNull(sumIf(valortotalprodutocomdesconto, statusfaturamento=‘CANCELED’)),
0,
sumIf(valortotalprodutocomdesconto, statusfaturamento=‘CANCELED’)
)

Col 3:
ifelse(
Col 1 - coalesce({Col 2}, 0) = 0,
0,
Col 1 - coalesce({Col 2}, 0)
)

Col 4:
ifelse(
{Col 2}= 0,
0,
coalesce({Col 3}, 0) - coalesce({Col 2}, 0)
)

image
image435×638 29.7 KB

I would like to know if there’s any error in my calculated filds!

2

Hi @glopes

I don’t understand when I do Col 1 - Col 2 at the Total level it matches the value of Col 3 at Total level. So where is the gap?

Essentially Col 3 = Col 1 - Col 2 which seems to be calculating right. Can you elaborate what is the issue?

Regards,
Giri

3

Hi @Giridhar.Prabhu.

Actually, the issue is in col 4. The total sum is supposed to be -6.169,21. When I add a filter to hide the rows with the value 0,00 in col 4, the total sum display correctly:

image
image418×444 21.8 KB

4

Hi @glopes,
So adding in the filter corrected your issue or you’re still looking for a work around on this?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

Hi @glopes,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!