3 Replies Latest reply on Aug 30, 2017 10:07 AM by Jim Dehner

# Average on Measure Values when null values

Hi all,

I have 3 KPIs and want to calculate the average for measure values.

First Approach (AvgTest1):

KPI1+KPI2+KPI3/3

(didn´t work because I have null values)

Second Approach (AvgTest2):

zn(KPI1)+zn(KPI2)+zn(KPI3)/3

(didn´t work because (/3). For East instead of 2+3/3=1.67 the correct result is 2+3/2=2.5

Any help on this ?

Mafalda

• ###### 1. Re: Average on Measure Values when null values

This is the formula that you wanted -

sum((zn([Kpi1])+zn([Kpi2])+zn([Kpi3])))/ ( countD(([Kpi1]))+countd([Kpi2])+countd([Kpi3]))

if will produce this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Average on Measure Values when null values

Great Jim

Thanks a lot

• ###### 3. Re: Average on Measure Values when null values

Thanks