Zeros are not getting reflected in the Pivot Tables and affects other calculations too

Question & Answer
,
1

Hi all, I’m unable to show Zeros in the pivot tables. This affects my other calculations (Difference, % of total, %Difference, etc.,) too in the pivot. For example: In the below image, I have added one column and two values in the field wells.

image
image1183×526 154 KB

Let’s take Last row:

  1. B1 is the difference of ({A1-A2} *10000). As we don’t see Zero % in A2, instead of {A1-A2} *10000), it works as ({A1-A3} *10000), Which is not correct.
  2. The output should be -8740, but as we don’t see zero% (just a blank) in the A2, the calculations are not performed, and no output is shown.

I have done some workarounds such as using 1.Coalesce and 2.Ifelse function both at the dataset and analysis Level to make the null as “0”. 3. Also tried “More formatting options” -->Null values -->Show custom as “0”. But still, it is not working.

2

If A2 is a calculated field then you need to check that calculation and try to add that ifelse logic there.

1 Like