Agregaciones en fórmulas

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 efectuaron en un mes?

Promedios    ¿Cuál fue el promedio mensual de ventas por comercial?

Valores mínimos y máximos    ¿Cuáles fueron las cinco principales zonas de ventas 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 integrar los grupos en Excel, debe escribir o seleccionar de forma manua 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 contienen identificadores de producto pero no los nombres de producto ni sus categorías. Para agregar la categoría a una hoja de cálculo de Excel plana, tendría que copiarla en la columna que contenía los nombres de categoría. Sin embargo, en un libro PowerPivot, puede importar la tabla de categorías de producto a su libro, 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 Relaciones entre tablas.

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 utilizan en las agregaciones, como sumas, promedios, mínimos o recuentos. Sin embargo, PowerPivot para Excel le permite crear fórmulas personalizadas para la agregación, además de las agregaciones estándar que se encuentran en 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, sin contar la ventana de mantenimiento de fin de mes?

Proporciones utilizando los promedios en periodos de tiempo ¿Cuál ha sido el crecimiento o descenso porcentual en ventas con respecto al mismo periodo del año anterior?

Valores mínimos y máximos agrupados ¿Cuáles fueron las principales zonas de venta para cada categoría de producto o para cada promoción comercial?

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, PowerPivot para Excel intentarán 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 de datos son una característica nueva de Excel y de PowerPivot para Excel que le permiten agrupar, ordenar y filtrar de forma interactiva los resultados en una tabla dinámica.

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 generar fórmulas que utilicen búsquedas, vea Relaciones y búsquedas en las fórmulas.

Una nueva característica de PowerPivot 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 utiliza para calcular las agregaciones. Los filtros se pueden utilizar en fórmulas tanto en columnas calculadas como en las medidas.

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, vea Filtrar datos en las fórmulas.

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.

Aunque estas funciones tienen los mismos nombres que sus homólogos de Excel, utilizan el motor VertiPaq de PowerPivot y se han reescrito para poder usarse con tablas y columnas. Las fórmulas de DAX no se pueden usar en un libro de Excel y viceversa. Solo se pueden utilizar en la ventana de PowerPivot y en las tablas dinámicas que están basadas en datos de PowerPivot. 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 individuales 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 y, por tanto, 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, Amount. Sin embargo, en PowerPivot, la fórmula se interpreta como "Obtener el valor de Amount para cada fila de la tabla Sales y, a continuación, sumar esos valores individuales". PowerPivot evalúa cada fila en la que se realiza la agregación y calcula un solo valor escalar. Después, realiza una agregación con 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 de las fórmulas DAX.