Tablas dinámicas imposibles 1 – Campos calculados

Esta es la primera publicación de una serie llamada Impossible PivotTables. El propósito de esta serie es explorar Power Pivot. Pensé que una forma divertida de hacerlo sería demostrar cómo el uso del modelo de datos nos permite crear tablas dinámicas que son imposibles con las tablas dinámicas tradicionales o que requieren soluciones alternativas. Espero que proporcione una forma divertida de examinar Power Pivot

¿Tablas dinámicas imposibles?

Históricamente, cuando intentaba crear una tabla dinámica (PT) tradicional que no era compatible con Excel, tenía que encontrar algún tipo de solución alternativa. Estas soluciones no siempre fueron bonitas, pero me ayudaron a obtener los números que necesitaba. Usaría soluciones como agregar columnas auxiliares a la tabla de datos, copiar y pegar varias tablas de datos en una sola tabla de fuente de datos, hacer clic y arrastrar para ordenar las etiquetas manualmente o crear fórmulas fuera del PT en la hoja de trabajo. Todos estos funcionaron, más o menos, pero no se sentían muy elegantes. Tampoco se sentían muy confiables… se sentían frágiles, como si pudieran romperse fácilmente en períodos futuros cuando tuviera que actualizar el informe. Luego, todo cambió cuando conocí Power Pivot (PP).

Básicamente, Power Pivot nos permite combinar la capacidad matemática de los informes basados ​​en fórmulas con la función de tabla dinámica. Nos permite crear informes PT que no requieren las soluciones alternativas mencionadas anteriormente. El resultado es un informe limpio y confiable que es fácil de actualizar y mantener a lo largo del tiempo. Y, sinceramente, simplemente se sienten mejor.

Entonces, basta de cháchara de fondo, vamos a construir nuestra primera tabla dinámica imposible.

Objetivo

Las tablas dinámicas tradicionales son excelentes para resumir y agregar valores almacenados dentro de una tabla de origen de datos. Cuando necesite que su informe calcule valores que no están incluidos en la fuente de datos, puede crear campos calculados. Sin embargo, esta característica no es muy sólida y tiene limitaciones. Por ejemplo, un campo calculado puede operar con valores dentro del informe, pero no con valores fuera del informe en otro rango o tabla. Entonces, cuando nos encontramos con esta limitación, intentamos solucionarla. Por ejemplo, podemos agregar una columna auxiliar a la tabla de datos o decidir realizar los cálculos fuera del PT. Pero estas soluciones tienen problemas. Por ejemplo, agregar una columna auxiliar en la tabla de datos puede no proporcionar los resultados matemáticos deseados en un informe determinado. Es decir, es posible que las matemáticas deban operar en subtotales o totales agregados en lugar de en cada fila. Y cuando creamos fórmulas fuera del PT, no se actualizan junto con el PT… lo que significa que debemos cuidarlas para asegurarnos de que se llenen para nuevas filas.

Para ilustrar este problema, proporcionaré un informe de ejemplo que calcula la comisión en función de los datos de ventas. Digamos que tenemos varias transacciones de ventas, como se muestra a continuación.

Luego, tenemos las tasas de comisión y los valores base de cada representante en otra tabla, como se muestra a continuación.

El informe que nos gustaría crear sumará las transacciones de ventas, restará el monto de ventas base y luego multiplicará el monto de ventas netas resultante por la tasa de comisión correspondiente. Buscamos algo como esto:

Antes incluso de empezar a crear el informe con un PT tradicional, nos encontramos con un problema. Un PT tradicional admite una tabla de datos de fuente única, pero nuestros datos vienen en dos tablas. Pero dejemos de lado ese hecho por el momento y centrémonos en lo que podemos hacer. Podemos usar fácilmente un PT tradicional para resumir las ventas por representante, así que comenzamos con eso. Creamos el PT e insertamos los campos RepID y Sales.

A continuación, intentamos crear un campo calculado para calcular los valores de las comisiones. La fórmula básicamente usaría BUSCARV para recuperar la tasa de comisión y el monto base para cada representante. Abrimos el cuadro de diálogo Campo calculado y cuando ingresamos una fórmula que intenta hacer referencia a valores fuera del PT, como la tabla de tasas de comisión, recibimos el siguiente mensaje de error:

Entonces, rápidamente llegamos a la conclusión de que se trata de una tabla dinámica imposible e intentamos encontrar una solución alternativa inteligente. Por ejemplo, intentamos utilizar una columna auxiliar en la tabla de datos para recuperar las tasas de comisión. Y eso funciona, pero cuando vamos a calcular los montos de las comisiones, nos damos cuenta de que necesitamos sumar los valores de las ventas y restar la base antes de aplicar la tasa. Así que llegamos a un callejón sin salida con eso e intentamos algo más.

Procedemos a calcular la comisión fuera del PT en celdas normales de Excel. Cuando hacemos esto, el informe final ni siquiera es un PT… es un informe basado en fórmulas que hace referencia a un PT intermedio para los valores de ventas agregados. Y si bien proporciona las cifras que necesitamos para este mes, ¿qué pasa con el próximo mes? Cuando pensamos en el futuro, nos damos cuenta de que este enfoque es frágil y puede fracasar el próximo período cuando actualicemos el informe. Cuando las fórmulas se escriben fuera del PT, no se incluirán cuando se actualice el PT. Entonces, con suerte recordaremos completar las fórmulas manualmente para incluir nuevos representantes. Y, como puedes imaginar, aquí es donde Power Pivot entra para ayudarnos.

Esta tabla dinámica es posible cuando usamos Power Pivot en lugar de una tabla dinámica tradicional… y no se necesitan soluciones alternativas

Descripción general

Construiremos esta tabla dinámica siguiendo los siguientes pasos:

• Cargue las tablas en el modelo de datos.
• Construya la tabla dinámica básica
• escribe las medidas

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.

Cargue las tablas en el modelo de datos.

Primero, necesitamos cargar las tablas en el modelo de datos y relacionarlas. Para hacer esto, seleccionamos cualquier celda en nuestra tabla de tasas de comisión y hacemos clic en el comando Power Pivot Agregar al modelo de datos .

Lo volvemos a hacer para la tabla que almacena las transacciones de ventas. Seleccione cualquier celda en la tabla de datos y haga clic en el comando Power Pivot Agregar al modelo de datos .

Mi forma favorita de relacionar estas dos tablas es mediante la vista de diagrama, por lo que, dentro de la ventana de Power Pivot, hacemos clic en Inicio Vista de diagrama . Podemos ver las dos tablas, como se muestra a continuación.

A continuación, debemos decirle a Excel cómo se relacionan estas tablas entre sí, es decir, qué columna se comparte entre ellas. En nuestro caso, es la columna RepID. Entonces, hacemos clic y arrastramos el RepID de una tabla a otra. Excel agrega la línea de relación, como se muestra a continuación.

Una vez completado esto, es hora de construir nuestro PT básico.

Construya la tabla dinámica básica

Para iniciar nuestra tabla dinámica, usamos el comando Insertar Tabla dinámica de Excel . En el cuadro de diálogo resultante, queremos utilizar el modelo de datos de este libro , como se muestra a continuación.

A continuación, insertamos el campo CommissionRates[RepID] en el área Filas , y los campos Transactions[SalesAmount] y CommissionRates[Base] en el área Values . El informe básico se muestra a continuación.

Con nuestro PT básico en buen estado, es hora de hacer los cálculos restantes escribiendo un par de medidas.

escribe las medidas

Primero, debemos restar las ventas base de la suma de las ventas para determinar el monto de las ventas netas comisionables. Para hacer esto, usamos el comando Power Pivot Medidas Nueva medida . En el cuadro de diálogo resultante, ingresamos el nombre de la medida deseada, NetSales, y la fórmula correspondiente como se muestra a continuación.

Luego, repetimos los pasos para crear nuestra siguiente medida, Comisión, que multiplica la medida NetSales por la tasa de comisión, como se muestra a continuación.

Podemos colocar la medida NetSales, el campo Tasa y la medida Comisión en el área de valores de la tabla dinámica, y el informe actualizado se muestra a continuación.

Y mire… no hay soluciones a la vista. Es como si Power Pivot hiciera posible una tabla dinámica imposible

Si desea investigar los detalles, consulte el archivo de muestra a continuación. Y si tiene otros consejos divertidos sobre Power Pivot, compártalos publicando un comentario a continuación.

Archivo de muestra

• Comisión2.xlsx