Promedio del total acumulado con Excel

En relación a un viejo post sobre totales acumulados con tablas dinámicas (del 2009) un lector me consulta: "Este es el acumulado de SUMA..¿Hay alguna posibilidad de obtener el acumulado de PROMEDIOS?
La intención es calcular el promedio del total acumulado en una tabla dinámica así como podemos calcular el total acumulado.

Todo, o casi todo, se puede hacer con Excel pero a su vez, hay más de una forma de hacerlo. La herramienta a usar depende de una serie de factores como el tamaño de base de datos, la configuración de los datos, tarea repetitiva o no, etc. Las herramientas que Excel pone a nuestra disposición son (desde la versión 2010 en adelante):

• fórmulas;
• tablas dinámicas;
• Power Query;
• PowerPivot.

Fórmulas


Si nuestra base de datos es pequeña, no se requiere transformar datos y no se trata de una tarea repetitiva, una sencilla tabla con fórmulas es suficiente. Veamos este ejemplo

La fórmula en la columna Total acumulado es =SUMA($B$2:B2). El "truco" aquí es anclar la primer celda del rango ($B$2) de manera que al copiarla al resto de las celdas de la columna el rango se vaya incrementando.

Lo mismo hacemos en la columna Promedio acumulado: =PROMEDIO($B$2:B2).

Podemos representar estos datos en un gráfico que nos muestre cómo se comportan las ventas mensuales en relación al promedio del acumulado

Tablas dinámicas.

Si nuestra base de datos tiene un tamaño considerable, los datos no están agregados como en la tabla anterior (por ejemplo, datos diarios cuando queremos calcular totales mensuales) pero no se requieren transformaciones, la herramienta indicada será una tabla dinámica.
Por ejemplo, a partir de una base de datos de ventas diarias de un año (más de 1000 filas) podemos crear esta tabla dinámica

La columna Acumulado la creamos como mostré en este post.
La pregunta ahora es si podemos crear una columna de promedio del acumulado como en la tabla anterior. La respuesta es: no, por lo menos no en forma directa.

Power Query

Con Power Query podemos resolver el problema con bastante facilidad (todo a través de la interfaz del usuario y usando funciones del PQ) con la ventaja de poder realizar transformaciones en los datos en caso de ser necesario pero sin el"dinamismo" de las tablas dinámicas (de hecho podemos imitar el dinamismo de una tabla dinámica como mostré en este post).

Después de crear una conexión a la base de datos aplicamos estos pasos:

1. creamos una columna con el año;
2. creamos otra con el mes;
3. quitamos la columna de las fechas (que ya no necesitamos);
4. agrupamos por año y mes y la columna Ventas con la función Sum;
5. agregamos una columna índice (1,1) y le cambiamos el nombre al paso a index (ésto por comodidad al escribir las fórmulas a continuación);
6. agregamos la columna Acumulado combinando las funciones List.Sum y List.Range (ver el código);
7. lo mismo para crear la columna Promedio combinando List.Average y List.Range;
8. finalmente quitamos la columna Índice que ya no necesitamos.

Este es el código de la consulta

 let  
   Origen = Excel.CurrentWorkbook(){[Name="facturas"]}[Content],  
   #"Año insertado" = Table.AddColumn(Origen, "Año", each Date.Year([Fecha]), Int64.Type),  
   #"Nombre del mes insertado" = Table.AddColumn(#"Año insertado", "Nombre del mes", each Date.MonthName([Fecha]), type text),  
   #"Columnas quitadas" = Table.RemoveColumns(#"Nombre del mes insertado",{"Fecha"}),  
   #"Filas agrupadas" = Table.Group(#"Columnas quitadas", {"Año", "Nombre del mes"}, {{"Ventas", each List.Sum([Ventas]), type number}}),  
   index = Table.AddIndexColumn(#"Filas agrupadas", "Índice", 1, 1),  
   acumulado = Table.AddColumn(index, "Acumulado", each List.Sum(List.Range(index[Ventas],0,[Índice])), type number),  
   promedio = Table.AddColumn(acumulado, "Promedio", each List.Average(List.Range(index[Ventas],0,[Índice])), type number),  
   #"Columnas quitadas1" = Table.RemoveColumns(promedio,{"Índice"})  
 in  
   #"Columnas quitadas1"  

que descargamos a una hoja de Excel con este resultado

Sobre la función List.Sum ya hemos hablado en el pasado; pueden consultar, por ejemplo, esta nota.

PowerPivot.

De todas las herramientas de Excel PowerPivot es la más poderosa. PowerPivot nos permite disfrutar de todas las ventajas de las tablas dinámicas tradicionales sin las limitaciones (un ejemplo son los elementos calculados que nunca funcionaron bien, en mi opinión).

Para poder utilizar PowerPivot en forma eficiente tenemos que realizar dos acciones:

1. cargar la base de datos al modelo de datos;
2. crear una "tabla de fechas" (calendario) en el modelo de datos y ligar ambas tablas.

Dado que nuestro ejemplo es muy sencillo, este segundo paso no es indispensable. De todas maneras lo utilizaremos como paso de "buena práctica". La "inteligencia de tiempo", tal vez la característica más notable de PowerPivot, puede ser explotada sólo con tablas de fechas.

Empezamos por cargar la tabla de ventas al modelo de datos: 

El próximo paso es crear la tabla de fechas. PowerPivot puede hacerlo en forma automática al detectar una columna de fechas en alguna de las tablas del modelo. También podemos crearla en una hoja de Excel y cargarla como tabla al modelo. La condición "sine qua non" para que sea tabla de fechas es que debe contener todos los días del período (en nuestro ejemplo 365 días ya que el año es el 2017).

En nuestro caso he creado la tabla en una hoja de Excel (ver la nota sobre creación de series) y le agregado una columna con el nombre del mes usando la función TEXTO

Una vez cargada al modelo de datos debemos marcarla como tabla de fechas

Creamos una relación entre ambas tablas ligando los campos "Fecha"



Ahora creamos una tabla dinámica

y ponemos el campo "Mes Nombre" de la tabla "Calendario" en el área de las filas (deben resistir la tentación de poner el campo "Fechas" de la tabla "Invoices").

Por defecto, Excel ordena el campo alfabéticamente así que usamos la posibilidad de ordenar el campo de la tabla

Como puede verse, a pesar que los valores del campo son textuales, Excel los interpreta como fechas ya que están ligados a una tabla marcada como "tabla de fechas".

En lugar de arrastrar el campo Ventas de la tabla Invoices al área de los valores, vamos a crear la medida Total Mensual

Podríamos haber usado el campo Ventas con, aparentemente, el mismo resultado. Las ventajas de usar una medida se verán a continuación.

Creamos una segunda medida: Acumulado

El código de la medida es

 CALCULATE (  
   SUM ( Invoices[Ventas] );  
   FILTER (  
     ALL ( 'Calendario'[Fecha] );  
     'Calendario'[Fecha] <= MAX ( 'Calendario'[Fecha] )  
   )  
 )  

Como este post está orientado a usuarios de nivel básico en PowerPivot (lo que no quita que sean de nivel intermedio o avanzado en Excel Clásico), no entraré en los detalles de la medida. Si espero que le abra el apetito a aquellos usuarios que estén dando los primero pasos en PowerPivot o a aquellos que estén considerando empezar a usarlo.

Arrastramos ambas medidas al campo de los valores

Ahora tenemos que crear la medida Promedio Acumulado. Para simplificar el código de la medida empezamos por agregar una columna calculada a la tabla Calendario. Para eso vamos a la ventana del modelo de datos, activamos la tabla Calendario y agregamos la columna Mes Numero usando la función MONTH de DAX (que es "gemela" de la función MES de Excel)

Para calcular el promedio del acumulado podemos dividir la medida Acumulado por el número de meses transcurridos (para eso incluimos la columna Mes Numero en la tabla Calendario).
Para simplificar la medida creamos una medida "intermedia": meses transcurridos en la tabla Calendario (siempre es importante crear las medidas en el contexto adecuado)

Finalmente creamos la medida Promedio acumulado dividiendo la medida Acumulado por la medida que acabamos de crear

y la agregamos a la tabla dinámica

¡Misión cumplida!

Ahora un pequeño detalle: nuestra tabla dinámica muestra valores en la fila Total general también para los campos Acumulado y Promedio acumulado. Para evitar que esto suceda podemos utilizar la técnica que mostré en la versión mejorada del post "Total Anual Móvil con PowerPivot". Creamos dos nuevas medidas: Acumulado st y Promedio st (donde st significa "sin totales")

Acumulado st = =IF(HASONEVALUE(Calendario[Mes Nombre]);[Acumulado];BLANK())

Promedio st = IF(HASONEVALUE(Calendario[Mes Nombre]);[Promedio acumulado];BLANK())

y las ponemos en la tabla dinámica en lugar de las medidas anteriores

Enlace para descargar el archivo.