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

Total Anual Móvil con PowerPivot - versión mejorada

En el post anterior mostré como calcular el TAM (Total Anual Móvil) con PowerPivot. La solución tiene algunos problemas:

1. no muestra los totales para cada año;
2. el cálculo del TAM promedio es inexacto para el primer año (2015 en nuestro ejemplo).

Aparentemente podemos solucionar el primer problema con facilidad; sencillamente agregamos los subtotales para el campo Year (el campo que arrastramos al área de las filas), pero entonces nos topamos con un nuevo problema

Total Móvil Anual (TAM) con PowerPivot

Como ya he declarado alguna vez en el pasado, lo prometido es deuda. A un poco más de dos meses de haber publicado el segundo post sobre como calcular el total anual móvil donde prometí mostar como calcularlo con PowerPivot, paso a saldar mi deuda (mis pocos pero buenos lectores sabrán disculpar).

PowerPivot es imbatible en el área de cálculos que impliquen comparación de períodos (lo que en inglés se da por llamar "Time Intelligence"). Y lo vamos a mostrar con el tema que estamos tratando, el cálculo del total anual móvil.

En este post parto de la suposición que el lector tiene un  conocimiento adecuado de PowerPivot y en particular del lenguaje DAX, y si no es así, espero que esta nota sirva de aliciente para impulsarlo a estudiar estas herramientas.

Vamos al grano. Para este ejemplo vamos a usar una tabla de datos diarios (ventas) del período 2015-2018.

Nuestra tarea es calcular el total anual móvil (TAM) para cada mes, es decir, totalizar el total mensual y compararlo con el acumulado de los últimos doce meses. Luego podemos calcular el promedio mensual del TAM y compararlo con las ventas del mes.

Total Móvil Anual (TAM) con Power Query

En el post anterior vimos como calcular el Total Anual Móvil usando fórmulas en Excel Clásico y prometimos mostrar como hacerlo con Power Query. Lo prometido es deuda, así que aquí va la explicación.

Empezamos por la misma tabla de datos de la nota anterior

Para calcular el TAM de Enero tenemos que sumar las ventas desde febrero del 2017 a enero del 2018 incluido, es decir los últimos doce meses. De la misma manera para febrero: desde marzo del 2017 a febrero del 2018 y así sucesivamente.

Veamos cómo creamos el informe con Power Query

Totales acumulados con Power Query

No me considero un profesional experto en Excel y aledaños (Power Query, PowerPivot), pero si un usuario experimentado. Por ese motivo las notas de este blog reflejan más mi trabajo cotidiano con esta fantástica herramienta que un enfoque técnico-profesional.

Una vez dicho ésto, vayamos al punto de la nota: en algún momento de nuestra vida tuvimos o tendremos que calcular totales o saldos acumulados ("running totals" en inglés).

En Excel Clásico (Excel Clásico = Excel sin Power Query y PowerPivot) sabemos hacerlo con fórmulas o, mejor aún, con tablas dinámicas. ¿Por qué, entonces, hacerlo con Power Query? Porque puede ser parte de una serie de transformaciones que estamos haciendo dentro de la consulta o porque, sencillamente, nos resulte más eficiente.

Sea cual sea el motivo vamos a ver cómo hacerlo en el entorno del Power Query.