Promedio sin contar valores cero.

Para el siguiente ejemplo, tenemos una serie de provincias con unas unidades vendidas, pero hay provincias que han vendido cero unidades.

El modelo esta en formato de tabla, y, se llama provincias.

Queremos calcular el promedio, pero de las provincias que si tienen unidades vendidas.

Veamos mas claro el problema, si utilizo la funcion CONTAR para contar el numero de cantidades que hay en la columna unidades.

Vemos que nos devuelve 20, incluyendo los ceros.

Ahora, con la funcion SUMA, voy a sumar la columna unidades.

Para calcular el promedio, debemos de dividir la suma de la columna cantidad entre la cantidad total de la columna unidades.

Vemos que nos devuelve 53,15, pero claro el valor cero se ha contado como una cantidad más, es decir, se ha dividido entre 20, cuando se debería de haber dividido entre 17.

Pues, este es el problema que vamos a resolver.

Con una celda dentro del modelo, vamos a la pestaña de insertar, y, hacemos clic en tabla dinámica.

En la ventana que se abre, marcamos la casilla hoja de calculo existente, la colocamos al lado del modelo, y, marcamos la casilla agregar estos datos al modelo de datos.

Aceptamos.

Bajamos a filas la columna provincias, y, a valores la columna unidades.

Hacemos clic en la punta de flecha al lado de suma de unidades en valores, y, seleccionamos configuración de campo de valor.

En la ventana de configuración de campo de valor, seleccionamos promedio, y, aceptamos.

Vemos que tenemos el promedio que calculamos manualmente.

En Power Pivot, vamos a crear una medida, donde nos debe de calcular el promedio siempre que la columna unidades sea diferente a cero, para ello, vamos a la pestaña de Power Pivot, dentro del grupo cálculos, desplegamos medidas, y, seleccionamos nueva medida.

Las funciones que vamos a utilizar las voy a usar de forma independiente para ir viendo los resultados.

En la ventana de medida, dejo el nombre predeterminado, en la ventana de formula, voy a usar la funcion CALCULATE, como argumento expresión, voy a usar la funcion DISTINCTCOUNT, que cuenta el numero de valores distintos dentro de una columna, el único argumento de esta funcion es columna, pues ponemos la columna de unidades.

=CALCULATE(

DISTINCTCOUNT(Provincias[Unicades])

Punto y coma, como argumento filter1, seleccionamos la columna unidades, ponemos el operador de comparación mayor (>), y, ponemos cero.

Cerramos paréntesis.

=CALCULATE(

DISTINCTCOUNT(Provincias[Unicades]);

Provincias[Unicades]>0)

Hacemos clic en comprobar formula.

Vemos que no se han detectado errores.

Aceptamos.

Se añade la medida a la tabla dinámica, donde vemos que aparece 1 donde es mayor a cero, y, nada donde es igual a cero.

Volvemos a la pestaña de Power Pivot, desplegamos medidas, administrar medidas.

En la ventana administrar medidas, seleccionamos medida 1, y, hacemos clic en editar.

Ya tenemos el numero de cantidades por la que tenemos que dividir la suma de la columna unidades, por lo que después del signo igual, ponemos la funcion DIVIDE.

Como argumento numerador, usamos la funcion SUM, para sumar la columna de unidades.

=DIVIDE(

SUM(Provincias[Unicades])

Punto y coma, como argumento denominador, en la funcion CALCULATE que hemos creado anteriormente.

Cerramos paréntesis.

=DIVIDE(

SUM(Provincias[Unicades]);

CALCULATE(

DISTINCTCOUNT(Provincias[Unicades]);

Provincias[Unicades]>0))

Aceptamos.

Y ya tenemos el promedio excluyendo las celdas que contienen ceros.

Si lo calculamos manualmente veremos que tenemos el mismo resultado.

Otra forma de hacerlo es también con CALCULATE, vamos a añadir una nueva medida.

Como argumento expresión, usamos la funcion AVERAGE aplicada a la columna unidades.

=CALCULATE(

AVERAGE(Provincias[Unicades])

Punto y coma, como argumento filter1, ponemos la misma condición, que la columna unidades sea diferente a cero, también, vale poner mayor a cero.

Cerramos paréntesis.

=CALCULATE(

AVERAGE(Provincias[Unicades]);

Provincias[Unicades]<>0)

Aceptamos, y, vemos que tenemos el mismo resultado.

La diferencia del primer método es calcular el promedio de forma manual, y, en el segundo método, usamos la funcion AVERAGE.