Average of Amount over the selected Year, but previous Year has null in the Amount field

[retko1985]

Hello,

please refer to the attached file.I made the Inline data, where I have 2 Years, Months and Amounts. In 2016 the Amounts are NULL.

I want to make an average, of the Amount when 2 Years are selected, but this does not work now:

sum(Amount1) / (12 * GetSelectedCount(Year))

I got number 5, when 2 Years are selected, but should be 10.

Next Year I will have data for this Amount for both Years.

I cannot use AVG!, because of nested aggregation, since I already do sum (so this example is not precise)

Can you please suggest better expression?

Thank you.

[prat1507]

Maybe this

=avg(Aggr(Sum(Amount1),Year,Month))

Regards

Pratyush

[sunny_talwar]

You can also try this

=Sum(Amount1)/Count(DISTINCT Year&Month)

[tresesco · Accepted solution]

If you have real nulls in the data, you could try like:

=sum(Amount1) /(12 * Count(DISTINCT {<Amount1={'*'}>}Year))

If not really nulls but no amount (some other values like, space or other invisible characters), try like:

=sum(Amount1) /(12 * Count(DISTINCT {<Amount1={"=Len(Trim(Amount1))>0"}>}Year))

[retko1985]

still gives 5 in model example and doesnt work on my app.

[prat1507]

Ok, so what is it evaluating in your app?

Regards
Pratyush

[retko1985]

Perfect, Thank you.