Cambio de cálculos en una tabla dinámica con segmentaciones

Las segmentaciones en tablas dinámicas se pueden usar no solo de la manera clásica: para filtrar los datos de origen, sino también para cambiar entre diferentes tipos de cálculos en el área de valor:

Implementar esto es muy fácil: todo lo que necesita es un par de fórmulas y una tabla auxiliar. Bueno, todo esto lo haremos no en el resumen habitual, sino en el resumen construido según el modelo de datos de Power Pivot.

Paso 1. Conexión del complemento Power Pivot

Si las pestañas del complemento Power Pivot no están visibles en su Excel, primero deberá habilitarlo. Hay dos opciones para esto:

• Tab audio revelador - botón complementos COM (Desarrollador: complementos COM)
• Archivo – Opciones – Complementos – Complementos COM – Ir (Archivo — Opciones — Complementos — Complementos COM — Ir a)

Si esto no ayuda, intente reiniciar Microsoft Excel.

Paso 2: cargar datos en el modelo de datos de Power Pivot

Tendremos dos tablas como datos iniciales:

La primera es una tabla con las ventas, según la cual luego construiremos un resumen. La segunda es una tabla auxiliar, donde se ingresan los nombres de los botones del segmento futuro.

Ambas tablas deben convertirse a "inteligentes" (dinámicas) con un atajo de teclado Ctrl+T o equipo Inicio – Formato como tabla (Inicio — Formatear como tabla) y es conveniente darles nombres cuerdos en la ficha Constructor (Diseño). Que sea, por ejemplo, Ventas и Servicios.

Después de eso, cada tabla a su vez debe cargarse en el modelo de datos; para esto, usamos en la pestaña PowerPivot . Agregar al modelo de datos (Agregar al modelo de datos).

Paso 3. Cree una medida para determinar el botón presionado en la rebanada

Los campos calculados en una tabla dinámica por modelo de datos se denominan medidas. Vamos a crear una medida que muestre el nombre del botón presionado en el segmento futuro. Para hacer esto, en cualquiera de nuestras tablas, seleccione cualquier celda vacía en el panel de cálculo inferior e ingrese la siguiente construcción en la barra de fórmulas:

Aquí, el nombre de la medida va primero (botón presionado), y luego después de dos puntos y un signo igual, una fórmula para calcularlo usando la función VALORES DAX integrado en Power Pivot.

Si repite esto no en Power Pivot, sino en Power BI, entonces los dos puntos no son necesarios y en su lugar VALORES puede usar su contraparte más moderna: la función VALOR SELECCIONADO.

No prestamos atención a los errores en la parte inferior de la ventana que aparecen después de ingresar la fórmula; surgen porque aún no tenemos un resumen y una porción en la que se hace clic en algo.

Paso 4. Crear una medida para el cálculo en el botón presionado

El siguiente paso es crear una medida para diferentes opciones de cálculo dependiendo del valor de la medida anterior botón presionado. Aquí la fórmula es un poco más complicada:

Vamos a desglosarlo pieza por pieza:

1. Función SWITCH – un análogo de IF anidado – verifica el cumplimiento de las condiciones especificadas y devuelve diferentes valores dependiendo del cumplimiento de algunas de ellas.
2. Función CIERTO() – da un "verdadero" lógico para que las condiciones verificadas posteriormente por la función SWITCH funcionen solo si se cumplen, es decir, la verdad.
3. Luego verificamos el valor de la medida del botón presionado y calculamos el resultado final para tres opciones diferentes: como la suma del costo, la verificación promedio y la cantidad de usuarios únicos. Para contar valores únicos, use la función CUENTA DISTINTA, y para redondear – REDONDA.
4. Si no se cumple ninguna de las tres condiciones anteriores, se muestra el último argumento de la función CAMBIAR; lo configuramos como ficticio usando la función VACÍO().

Paso 5. Crear un resumen y agregar una porción

Queda por volver de Power Pivot a Excel y construir allí una tabla dinámica para todos nuestros datos y medidas. Para ello, en la ventana de Power Pivot en El principal comando de selección de tabulación tabla resumen (Inicio — Tabla dinámica).

Entonces:

1. tiramos al campo Producto de la mesa Ventas a la zona filas (Filas).
2. Tirando un campo ahí Resultado de la mesa Servicios.
3. Haga clic derecho en el campo Resultadoy elige un equipo Añadir como rebanada (Agregar como rebanador).
4. Lanzamiento del segundo compás Conclusión de la mesa Servicios a la zona Valores (Valores).

Aquí, de hecho, están todos los trucos. Ahora puede hacer clic en los botones de la segmentación y los totales en la tabla dinámica cambiarán a la función que necesita.

Belleza 🙂

• Beneficios de Pivot por modelo de datos
• Análisis de plan-hecho en una tabla dinámica en Power Pivot
• Cree una base de datos en Excel usando el complemento Power Pivot