Agregaciones en Power Pivot

Elegir grupos para la agregación

Al agregar datos, está agrupando estos datos por atributos como producto, precio, región o fecha y, a continuación, define una fórmula válida para todos los datos en el grupo. Por ejemplo, al crear un total para un año, está creando una agregación. Si posteriormente, crea una proporción para este año según el año anterior y la presenta como porcentaje, será un tipo de agregación diferente.

La decisión sobre cómo agrupar los datos depende de las cuestiones empresariales. Por ejemplo, se pueden plantear las siguientes preguntas paras las agregaciones:

Recuentos   ¿Cuántas transacciones se realizaron en un mes?

Promedios    ¿Cuáles fueron las ventas medias de este mes, por vendedor?

Valores mínimos y máximos    ¿Qué distritos de ventas fueron los cinco mejores en términos de unidades vendidas?

Para crear un cálculo que responda a estas preguntas, debe disponer de datos detallados que contengan las cifras de recuento o suma, y esos datos numéricos deben estar relacionados de cierta forma con los grupos que utilizará para organizar los resultados.

Si los datos aún no contienen valores que pueda utilizar para agrupar, como una categoría de producto o el nombre de la región geográfica donde se encuentra el almacén, podría ser conveniente introducir grupos para los datos agregando categorías. Al compilar los grupos en Excel, debe escribir o seleccionar de forma manual los grupos que desea utilizar de entre las columnas de la hoja de cálculo. Sin embargo, en un sistema relacional, las jerarquías como las categorías de productos se suelen almacenar en una tabla diferente a la de hechos o valores. Por lo general, la tabla de categoría se vincula a los datos de hecho a través de algún tipo de clave. Por ejemplo, suponga que descubre que sus datos los contienen identificadores de producto, pero no incluyen los nombres de producto ni sus categorías. Para agregar la categoría a una hoja de cálculo plana de Excel, tendría que copiarla en la columna que contenía los nombres de categoría. Con Power Pivot, puede importar la tabla de categorías de producto al modelo de datos, crear una relación entre la tabla con los datos de número y la lista de categorías de producto y, a continuación, usar las categorías para agrupar los datos. Para obtener más información, vea Crear una relación entre tablas.

Elegir una función para la agregación

Después de haber identificado y agregado las agrupaciones que se van a utilizar, debe decidir qué funciones matemáticas desea utilizar para las agregaciones. A menudo, la palabra agregación se utiliza como un sinónimo de las operaciones matemáticas o estadísticas que se usan en las agregaciones, como sumas, promedios, mínimos o recuentos. Sin embargo, Power Pivot le permite crear fórmulas personalizadas para la agregación, además de las agregaciones estándar que se encuentran en Power Pivot y Excel.

Por ejemplo, partiendo del mismo conjunto de valores y agrupaciones que se utilizaron en los ejemplos anteriores, podría crear agregaciones personalizadas que respondan a las siguientes preguntas:

Recuentos filtrados   ¿Cuántas transacciones se realizaron en un mes, excluyendo la ventana de mantenimiento de fin de mes?

Relaciones que usan promedios a lo largo del tiempo    ¿Cuál fue el porcentaje de crecimiento o disminución de las ventas en comparación con el mismo período del año pasado?

Valores mínimos y máximos agrupados    ¿Qué distritos de ventas se clasificaron en primer lugar para cada categoría de producto o para cada promoción de ventas?

Agregar agrupaciones a una tabla dinámica

Al diseñar una tabla dinámica, los campos que representan agrupaciones, categorías o jerarquías se arrastran a la sección de columnas y filas de la tabla dinámica para agrupar los datos. A continuación, los campos que contienen valores numéricos se arrastran al área de valores para que se puedan contar, promediar o sumar.

Si agrega categorías a una tabla dinámica pero los datos de categoría no están relacionados con los datos de hecho, podría obtener un error o resultados extraños. Normalmente, Power Pivot intentará corregir el problema; para ello, detectará y sugerirá relaciones automáticamente. Para obtener más información, vea Trabajar con relaciones en tablas dinámicas.

También puede arrastrar los campos hasta las segmentaciones de datos, para seleccionar ciertos grupos de datos y poder verlos. Las segmentaciones permiten agrupar, ordenar y filtrar interactivamente los resultados de una tabla dinámica.

Trabajar con agrupaciones en una fórmula

También puede utilizar agrupaciones y categorías para agregar los datos que se almacenan en tablas si crea relaciones entre las tablas y posteriormente fórmulas que aprovechen esas relaciones para buscar valores relacionados.

Es decir, si se desea crear una fórmula que agrupe los valores según una categoría, primero se debe usar una relación para conectar la tablas que contiene los datos detallados y las tablas que contienen las categorías, y a continuación se genera la fórmula.

Para obtener más información acerca de cómo crear fórmulas que usen búsquedas, consulte Búsquedas en fórmulas de Power Pivot.

Utilizar filtros en agregaciones

Una nueva característica de Power Pivot es la capacidad de aplicar filtros a las columnas y tablas de datos, no solo en la interfaz de usuario y dentro de una tabla dinámica o gráfico, sino también en las mismas fórmulas que usa para calcular las agregaciones. Los filtros se pueden usar en fórmulas tanto en columnas calculadas como en s.

Por ejemplo, en las nuevas funciones de agregación de DAX, en lugar de especificar valores para hacer sumas o recuentos, puede especificar una tabla completa como el argumento. Si no ha aplicado filtros a esa tabla, la función de agregación se articulará de acuerdo a todos los valores en la columna especificada de la tabla. Sin embargo, en DAX puede crear un filtro dinámico o estático en la tabla, de forma que la agregación se articula de acuerdo a un subconjunto diferente de datos en función de la condición de filtro y el contexto válido en esos momentos.

Al combinar condiciones y filtros en fórmulas puede crear agregaciones que van cambiando en función de los valores que se proporcionan en las fórmulas o en función de la selección de los encabezados de fila y encabezados de columna en una tabla dinámica.

Para obtener más información, consulte Filtrar datos en fórmulas.

Funciones de agregación estándar

Funciones de agregación de DAX

DAX incluye funciones de agregación con las que puede especificar una tabla donde se efectuará la agregación. Por lo tanto, en lugar de calcular simplemente la suma o el promedio de los valores de una columna, estas funciones permiten crear una expresión que define dinámicamente los datos que se agregarán.

En la tabla siguiente se enumeran las funciones de agregación disponibles en DAX.

Diferencias entre las funciones de agregación de DAX y Excel

Aunque estas funciones tienen los mismos nombres que sus homólogos de Excel, utilizan el motor de análisis en memoria de Power Pivot y se han reescrito para poder usarse con tablas y columnas. No puede usar una fórmula de DAX en un libro de Excel y viceversa. Solo se pueden utilizar en la ventana de Power Pivot y en las tablas dinámicas que están basadas en datos de Power Pivot. Además, aunque las funciones tengan nombres idénticos en inglés, el comportamiento puede ser algo distinto. Para obtener más información, vea los temas específicos de referencia de funciones.

La manera en que se evalúan las columnas en una agregación también es diferente del modo en que Excel administra las agregaciones. Esto puede ilustrarse mejor con un ejemplo.

Imagine que desea obtener una suma de los valores de la columna Amount de la tabla Sales, para lo que crea la siguiente fórmula:

=SUM('Sales'[Amount])

En el caso más simple, la función recibe los valores en una única columna sin filtrar y el resultado es igual que en Excel, que siempre suma solo los valores de la columna, Importe. Sin embargo, en Power Pivot, la fórmula se interpreta como "Obtener el valor de Importe para cada fila de la tabla Ventas y, a continuación, sumar esos valores individuales. Power Pivot evalúa cada fila sobre la que se realiza la agregación y calcula un valor escalar único para cada fila, y, a continuación, realiza una agregación de esos valores. Por consiguiente, el resultado de una fórmula puede ser diferente si se han aplicado filtros a una tabla o si los valores se calculan según otras agregaciones que se podrían filtrar. Para obtener más información, vea Contexto en fórmulas de DAX.