Impossible PivotTables 4 – Promedio de recuento distinto

Esta es la cuarta y última publicación de la serie Impossible PivotTables, donde exploramos Power Pivot observando algunas limitaciones encontradas con las tablas dinámicas tradicionales. En esta publicación, veremos cómo calcular el promedio cuando hay varias filas por artículo. Es decir, cuando una simple suma dividida por el número de filas no es suficiente y, en su lugar, necesitamos dividir la suma por un recuento distinto del número de elementos. Por ejemplo, cuando queremos los promedios de los pedidos, pero nuestros datos tienen varias líneas por pedido. Hagámoslo.

Objetivo

Antes de pasar al tema técnico, confirmemos nuestro objetivo.

Echemos un vistazo a una parte de la tabla de datos:

Al observar la tabla de datos, vemos que puede haber varios pedidos por día y que cada pedido puede tener varias líneas. Por ejemplo, el pedido 101 tiene dos líneas (filas) y el pedido 102 tiene 3 líneas.

Nos gustaría saber el monto promedio diario del pedido. Para calcular eso, sabemos que debemos sumar el monto total de cada día y dividirlo por el número de pedidos.

Probemos esto primero con un PT tradicional. Cuando insertamos los campos Fecha y Monto, vemos la suma como se esperaba:

Luego, hacemos clic derecho en cualquier valor en la columna Suma de monto y seleccionamos Resumir valores por Promedio . El PT actualizado se muestra a continuación.

El PT muestra un promedio, por lo que creemos que estamos listos para comenzar. ¿O… lo somos? Tras una inspección más cercana, podemos ver que el promedio de PT es simplemente la suma dividida por el número de filas de datos. Por ejemplo, el 1/1/2020 la suma es 800 y el número total de filas (líneas de orden) es 8. Entonces, nuestro PT muestra 800 dividido por 8, o 100. Pero ese no es el promedio que estamos buscando. para. Buscamos el pedido promedio , no la línea de pedido . Mmm.

El numerador (suma) es correcto. Pero el denominador no es lo que buscamos. El denominador es el número de filas en lugar del número de pedidos. Estos dos valores no son iguales porque hay varias filas de datos por pedido. Maldita sea. Llegamos a una limitación y ahora estamos estancados. ¿O… lo somos? Como puedes sospechar, aquí es exactamente donde Power Pivot viene a rescatarnos.

Descripción general

Calcularemos el monto promedio del pedido por día de la siguiente manera:

• Cargar el modelo de datos
• Configurar el PT básico
• escribe la medida

Hagámoslo.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que necesite descargar e instalar el complemento Power Pivot.

Cargar el modelo de datos

Para cargar la tabla en el modelo de datos, hacemos clic en cualquier celda de la tabla y usamos el comando Power Pivot Agregar al modelo de datos . La ventana de Power Pivot confirma que la tabla está en el modelo de datos como se muestra a continuación.

Con nuestra tabla en el modelo de datos, es hora de comenzar con nuestro PT básico.

Configurar el PT básico

Usamos el comando Insertar Tabla dinámica de Excel y confirmamos que optamos por Usar el modelo de datos de este libro como fuente, como se muestra a continuación.

Luego insertamos el campo Fecha en el área de diseño Filas y el campo Importe en el área Valores. El PT básico se muestra a continuación.

En este punto, muestra los mismos valores que el PT tradicional. Ahora es el momento de escribir una medida para calcular el promedio deseado.

escribe la medida

Usamos el comando Power Pivot Medidas Nueva medida de Excel para abrir el cuadro de diálogo Medir. El nombre de la medida será AvgOrder y la fórmula dividirá la suma del importe por el número de pedidos, como se muestra a continuación.

Analicemos la fórmula. Usamos la función DIVIDIR para realizar la división. El primer argumento es el numerador, que es la [Suma del importe]. El segundo argumento es el denominador, que utiliza la función DISTINCTCOUNT para contar el número de pedidos únicos en la columna OrderID.

Hacemos clic en Aceptar y el PT se actualiza, como se muestra a continuación.

Ahora, revisemos dos veces los cálculos para asegurarnos de que lo tenemos. Para el 1/1/2020, la cantidad total es 800, lo que se ve bien. Al inspeccionar nuestra mesa, vemos que hay 4 pedidos para ese día, por lo que 800 dividido por 4 son 200… ¡sí, funcionó! Y vemos que hay 1 pedido para el 2/1/2020, y 180 dividido por 1 es 180… ¡perfecto! Hay 3 pedidos el 3/1/2020, y 360 dividido por 3 es 120… ¡genial! Y comprobemos dos veces el total general. Vemos que hay 8 pedidos en total, y 1340 dividido entre 8 es 167,50… ¡Creo que lo tenemos!

Si tiene algún otro consejo de Power Pivot, compártalo publicando un comentario a continuación… ¡gracias!

Archivo de muestra:

• AvgDistinctItems.xlsx