Impossible PivotTables 2 – Mostrar valores como

Esta es la segunda publicación de la serie Impossible PivotTables, donde exploramos Power Pivot. En la primera publicación, examinamos la limitación de las referencias de campos calculadas en una tabla dinámica tradicional. En esta publicación, abordaremos la limitación de usar la opción Mostrar valores como para cálculos posteriores. Si utilizamos la opción Mostrar valores como para cambiar la forma en que se muestran los valores del informe, como un porcentaje del total general, obtenemos resultados inesperados cuando intentamos utilizar esos resultados en otros cálculos. Para ayudar a visualizar esto, pasemos a una ilustración y hablemos del objetivo general.

Objetivo

Nuestro objetivo es asignar un gasto de seguro corporativo de $10,000 a nuestros departamentos. Hemos decidido asignarlo en función del salario relativo de cada departamento. Mecánicamente, necesitamos calcular el porcentaje del salario de cada departamento y luego multiplicar los porcentajes por los $10 000 de gastos de seguro corporativo.

Entonces, tomamos nuestros datos salariales mensuales y comenzamos con una tabla dinámica tradicional. Colocamos el campo Departamento en filas e insertamos el campo Importe en el área de diseño de valores. Luego, hacemos clic derecho en cualquier celda de cantidad y seleccionamos Mostrar valores como % del total general . El PT resultante se muestra a continuación.

Hasta ahora, todo bien. Esto es perfecto. Ahora, todo lo que tenemos que hacer es aplicar estos porcentajes al monto de gastos corporativos de $10,000. Entonces, abrimos el cuadro de diálogo del campo calculado y escribimos una fórmula simple para multiplicar $10,000 por el campo de monto, como se muestra a continuación.

Cuando le damos a OK… ¡¡¡Ay!!!! Los resultados son escandalosos, como se muestra a continuación.

¿Qué acaba de pasar aquí?

El monto de asignación de cada departamento debe ser sólo una fracción de los $10,000. Y el total general de la asignación debería ser de $10,000. Cuando miramos la columna Suma de asignación, los resultados son, bueno, inesperados.

Cuando usamos el comando Mostrar valores como para mostrar la cantidad como porcentaje del total general, Excel cambió los valores mostrados… pero no los valores subyacentes del informe. Por lo tanto, la columna Suma de asignación en realidad muestra $10 000 multiplicado por el valor del Monto (no el porcentaje del total), como se muestra a continuación.

Y esto se convierte en otra situación en la que un PT tradicional no respalda el tipo de informe que estamos tratando de elaborar. Tenemos otra tabla dinámica imposible y recurrimos a algún tipo de solución, como usar una columna auxiliar o escribir las fórmulas fuera del PT. Pero, tan pronto como hacemos eso, el proceso de presentación de informes no parece limpio ni genuino. Se siente un poco frágil y nos preocupa que podamos tener problemas en el futuro si el informe alguna vez necesita una actualización.

¿Hay buenas noticias? Sí, claro. Una vez más, Power Pivot hará posible esta imposible Tabla Pivote.

Descripción general

Lo construiremos en tres pasos:

• Cargue la tabla en el modelo de datos.
• Ponga en marcha el PT básico
• escribe las medidas

Empecemos.

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 la tabla en el modelo de datos.

Primero, necesitamos cargar la tabla de datos en el modelo de datos. Seleccionamos cualquier celda en la tabla de datos de Salarios y hacemos clic en el comando Power Pivot Agregar al modelo de datos .

Bueno, eso fue fácil. Es hora de configurar nuestro PT básico.

Ponga en marcha el PT básico

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

Luego, agregamos el campo Departamento en las filas y el campo Importe en los valores. El PT actualizado se muestra a continuación.

Hasta aquí todo bien… ahora falta escribir algunas medidas.

escribe las medidas

Primero, necesitamos determinar el total de todos los salarios. Entonces, usamos el comando Power Pivot Medidas Nueva medida e ingresamos el nombre de la medida deseada, AllWages y la fórmula correspondiente, como se muestra a continuación.

Esta medida utiliza la función CALCULAR para calcular la SUMA de la columna Salarios[Cantidad] para TODAS las filas de la tabla Salarios.

La siguiente medida calculará el % del total , por lo que agregamos una nueva medida como se muestra a continuación.

Esta medida divide los salarios de cada departamento que se muestra en el informe por la medida [Todos los salarios]. Esto calculará los salarios como un porcentaje del total.

Y finalmente, sólo nos falta hacer la asignación. Por lo tanto, creamos una medida denominada Asignación para multiplicar la medida [% del total] por el gasto corporativo de $10 000, como se muestra a continuación.

El PT resultante se muestra a continuación.

Podemos solucionarlo eliminando la medida de todos los salarios, y bam… otra tabla dinámica imposible hecha posible con Power Pivot, como se muestra a continuación.

Si desea ver el archivo de Excel utilizado para esta publicación, simplemente consulte el archivo de muestra. Y si tiene otros consejos divertidos sobre Power Pivot, compártalos publicando un comentario a continuación.

Archivo de muestra

• Asignación.xlsx