Aggregating over null values

[debmsarkar123]

My dataset:

MonthYear              Name       Time (Mins)   Changes

Jan-2022	Ar	2760	120
Jan-2022	Ar	4218	111
Jan-2022	Ar
Jan-2022	Bel	529.2	36
Jan-2022	Bel	723.8	47
Jan-2022	Bel	768	48
Jan-2022	Bel	1410	100
Jan-2022	Bel		0
Jan-2022	Bel
Jan-2022	G	1722	82
Jan-2022	G	2300	115
Jan-2022	G	2850	114
Jan-2022	G
Jan-2022	M	127.6	4
Jan-2022	M	606.1	19
Jan-2022	M	1251.6	42
Jan-2022	M	1744.2	57
Jan-2022	M
Jan-2022	Sr		41
Jan-2022	Sr
Jan-2022	Tal		59.5
Jan-2022	Tal

Now I want to aggregate based on Name and Month year  and I dont want to consider the rows where we have Time(Mins) and Changes zero.

Hence I am using this expression-

aggr(
if(
(sum([Time (Mins)])
/
sum([Changes])) = 0, null(),

(sum([Time (Mins)])
/
sum([Changes]) ))
,
MonthYear,[Plant Name])

This is giving me 24.4 ( I have put Total mode as Average of rows in presentation tab)

but when I manually do it the answer come 23.48

More explanation on how I am manually calculating-

Im ignoring Sr , Tal rows as they have incomplete data( numerator or denominator missing) and other rows which are blank fully. I am summing the Time(mins) and Changes and then dividing it.

[Digvijay_Singh]

I think your manual calculation resembles this below - 

But if you aggr by Monthyear and name, it actually first groups values by that combination and then takes average, I have seen average function can give different values based on against which dimension you take the average.

[debmsarkar123]

Hi,

Thanks for your answer.  Is there any way to match the numbers for both scenarios?

[Digvijay_Singh]

Not sure caz excel also shows the same behavior..