How to display rows with zero values

[farheenayesha]

Hi,

     I am trying to show utilization % of sheet in pivot table. However i am not able to show the sheets with zero values in the pivot table.

I am having 3 documents(Doc1,Doc2 & Doc3) and each has got few sheets.I am facing two issues in my dashboard. 

1. Doc1 has got 14 sheets and out of which only 11 has utilization % and the other three doesn't have any data. For these 3 sheets i want to show value as zero. However with my expression, these three rows are not getting displayed in pivot table. Please find the data of the pivot table below.

Expression:Count(distinct UNIX_ID_GOV)/count(DISTINCT total UNIX_ID_RST)    

Sheet	GOV	RST	GOV/RST	Document
Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1

However, when i am checking 'Show all Values' at dimension level it is showing the other sheets of other docs as well (Doc2 & Doc3). Which I dont want. Please find the data below.

Sheet	GOV	RST	GOV/RST	Document
Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1
Sheet12	0	112	0.0%	Doc1
Sheet13	0	112	0.0%	Doc1
Sheet14	0	112	0.0%	Doc1
Sheet15	0	112	0.0%	Doc2
Sheet16	0	112	0.0%	Doc2
Sheet17	0	112	0.0%	Doc2
Sheet18	0	112	0.0%	Doc2
Sheet19	0	112	0.0%	Doc2
Sheet20	0	112	0.0%	Doc2
Sheet21	0	112	0.0%	Doc2
Sheet32	0	112	0.0%	Doc2
Sheet33	0	112	0.0%	Doc3
Sheet34	0	112	0.0%	Doc3
Sheet35	0	112	0.0%	Doc3
Sheet47	0	112	0.0%	Doc3
Sheet48	0	112	0.0%	Doc3
Sheet49	0	112	0.0%	Doc3
Sheet50	0	112	0.0%	Doc3
Sheet51	0	112	0.0%	Doc3
Sheet64	0	112	0.0%	Doc3
Sheet65	0	112	0.0%	Doc3

I want the Pivot as below:

Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1
Sheet12	0	112	0.0%	Doc1
Sheet13	0	112	0.0%	Doc1
Sheet14	0	112	0.0%	Doc1

2 . My second issue is showing the count of these sheets as 11. Currently with my expression(Count (distinct Sheet)) it is showing the count as 11. It is not counting the rows with zero values. I want the count of sheet as 14.

Kindly help me to resolve these 2 issues.

stalwar1‌@

[sunny_talwar · Accepted solution]

This

=Count(DISTINCT {<Tab_DocName = p(Tab_DocName), DocName>} Sheet)

[farheenayesha]

U solved my biggest problem again. Thank you soooooooooo much