Message-ID: <1838772851.23.1582873008338.JavaMail.root@www> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_22_2126254933.1582873008338" ------=_Part_22_2126254933.1582873008338 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Calculating average across different drill down levels

# Calculating average across different drill down levels

It happens very frequently that we use a few drill downs in our = tables, and we want to calculate the average of indicators at different lev= els.

=20
=20 Icon=20
=20

It is recommended to get familiar with memberValue and crossValue functi= ons before proceeding with this tutorial.

=20
=20
=20

In the table below, you can see two drill down levels (Employee = Name and Product). Avg. Rating i= s calculating the average rating for that employee on that product.

=20
=20 =20
=20

But you may want to compare the employee's average rating for a product = with the overall employee rating for the same product as a benchmark, as it= is shown in the Avg. Benchmark column. This could be adde= d as a conditional formatting, so if the employee's avg. rating on a produc= t is higher than overall employee's rating on this product, we can mark it = as green and show an upper arrow there.

=20

Here is how you could calculate this.

=20
=20
```if(!equals(substringBefore(memberValue(), ' ') ,'Product')) {=20
return '';=20
} else {=20
filter("L_PRODUCT in ('" + memberValue() + "')") {cro=
ssValue('CUBE_DAILY_CALLS.DAILY_CALLS','M_RATING@AVG')};=20
}
```
=20
=20
=20 Icon=20
=20

It is recommended to get familiar with crossValue formula before proceed= ing with this tutorial. As crossValue will not take drill downs applied in = the context view.

=20
=20
=20

In this table below, now Product is being placed as the= first level of drill downs, and then Employee Name is the= second level. Avg. Benchmark is still the overall employe= e average on the product. Changing the order of the drill downs will make c= alculating *Avg. Benchmark" a little bit different.

=20
=20 =20
=20

The code is as below:

=20
=20
```double benchmark =3D 0;=20
if(equals(substringBefore(memberValue(), ' '), 'Product')=20
|| isBlank(memberValue())) {=20
benchmark =3D M_RATING@AVG;=20
}else {=20
benchmark =3D prev('M_RATING@AVG',1);=20
}=20

return benchmark
```
=20
=20