Calculate total number of rows with 0 values in column

[ajaysinghciti]

Hi All, 

I have below input table and 

INPUT
ID	Measure1	Measure2	Measure1-Measure2
1	10	20	-10
2	20	30	-10
3	0	-	#VALUE!
4	0	-	#VALUE!
5	-	-	#VALUE!
6	-	-	#VALUE!
7	-	40	#VALUE!
8	-	50	#VALUE!

OUTPUT
ID	Measure1	Measure2	Measure1-Measure2
1	10	20	-10
2	20	30	-10
3	0	0	0
4	0	0	0
5	0	0	0
6	0	0	0
7	0	40	-40
8	0	50	-50

Requirement is below:-

1. - to be replace by 0 number

2. Calculate count of 0 values in Measure1 -Measure2 /Count of total records of ID which is 4/8

3. Calculate count of non 0 values in Measure1 -Measure2 /Count of total records of ID which is 4/8

pls help me derive this

[rubenmarin · Accepted solution]

Hi, Measure 1 could be: Alt(Measure1,0)

And Measure2: Alt(Measure2,0)

So the difference could be Alt(Measure1,0)-Alt(Measure2,0)

The % of zeros: Sum(Aggr(If(Alt(Measure1,0)-Alt(Measure2,0)=0,1),ID)) / Count(Distinct ID)

The% of non-zeros: Sum(Aggr(If(Alt(Measure1,0)-Alt(Measure2,0)<>0,1),ID)) / Count(Distinct ID)