Office
Iniciar sesión

Agregaciones en Power Pivot

Nota:  Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Las agregaciones son una manera de contraer, resumir o agrupar datos. Al empezar con datos sin procesar de tablas u otros orígenes de datos, los datos normalmente son planos, lo que significa que hay muchos detalles, pero no se han organizado ni se han agrupado de ninguna forma. Esta falta de resúmenes o de estructura podría dificultar el detectar los modelos de los datos. Una parte importante del modelado de datos consiste en definir agregaciones que simplifiquen o resuman los modelos como respuesta a una pregunta de negocio concreta.

Agregaciones más comunes, como los que usen promedio, contar, DISTINCTCOUNT, MAX, MINo suma pueden crearse en una medida automáticamente con Autosuma. Otros tipos de agregaciones, como AVERAGEX, COUNTX, COUNTROWSo SUMXdevuelven una tabla y requieran una fórmula creada con Expresiones de análisis de datos (DAX).

Descripción de los agregados de 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 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 ya no contiene valores que puede usar para la agrupación, por ejemplo, una categoría de producto o el nombre de la región geográfica donde se encuentra el almacén, que desee introducir los grupos a los datos mediante la adición de categorías. Al crear grupos en Excel, debe escribir manualmente o seleccione los grupos que desea usar entre las columnas de la hoja de cálculo. Sin embargo, en un sistema relacional, jerarquías como categorías de productos con qué frecuencia se almacenan en una tabla diferente de hecho o una tabla de valor. Normalmente la tabla categoría está vinculada a los datos de hechos por algún tipo de clave. Por ejemplo, suponga que encuentre que sus datos contienen los identificadores de producto, pero no los nombres de productos o sus categorías. Para agregar la categoría a una hoja de cálculo de Excel sin relieve, tendrá que copiar en la columna que contiene los nombres de categoría. Power Pivot, puede importar la tabla de categoría de producto en el modelo de datos, crear una relación entre la tabla con el número de datos y la lista de categorías de producto y, a continuación, utilice 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, 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?

Agregar agregaciones a fórmulas y tablas dinámicas

Una vez que se tiene una idea general de cómo se deben agrupar los datos para que sean significativos, y con qué valores se desea trabajar, se puede decidir si generar una tabla dinámica o crear cálculos en una tabla. Power Pivot amplía y mejora la capacidad nativa de Excel de crear agregaciones como sumas, recuentos o promedios. Las agregaciones personalizadas en Power Pivot se pueden crear en la ventana de Power Pivot o en el área de tabla dinámica de Excel.

  • En una columna calculada, se pueden crear agregaciones que tengan en cuenta el contexto de la fila actual para recuperar filas relacionadas de otra tabla y, a continuación, sumar, contar o promediar los valores de las filas relacionadas.

  • En una medida, se pueden crear agregaciones dinámicas que usan filtros definidos dentro de la fórmula y filtros impuestos por el diseño de la tabla dinámica y la selección de segmentación de datos, los encabezados de columna y los encabezados de fila. Medidas con agregaciones estándares pueden crearse en Power Pivot con Autosuma o creando una fórmula. También puede crear medidas implícitas con agregaciones estándares en una tabla dinámica en Excel.

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 para aplicar filtros a columnas y tablas de datos, no solo en la interfaz de usuario y dentro de una tabla dinámica o un gráfico, pero también en las fórmulas muy que usar para calcular las agregaciones. Filtros se pueden utilizar en las fórmulas en columnas calculadas y 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 en Excel y en DAX

En la siguiente tabla se incluye una lista de algunas de las funciones de agregación estándar que ofrece Excel y proporciona los vínculos a la implementación de estas funciones en Power Pivot. La versión DAX de estas funciones tiene un comportamiento muy parecido al de la versión de Excel, con algunas diferencias menores de sintaxis y tratamiento de determinados tipos de datos.

Funciones de agregación estándar

Función

Uso

AVERAGE

Devuelve el promedio (la media aritmética) de todos los números de una columna.

AVERAGEA

Devuelve el promedio (media aritmética) de todos los valores de una columna. Trata texto y valores no numéricos.

COUNT

Cuenta el número de valores numéricos en una columna.

COUNTA

Cuenta el número de valores de una columna que no están vacías.

MAX

Devuelve el mayor valor numérico de una columna.

MAXX

Devuelve el valor más grande de un conjunto de expresiones evaluado sobre una tabla.

MIN

Devuelve el menor valor numérico de una columna.

MINX

Devuelve el valor menor de un conjunto de expresiones evaluado sobre una tabla.

SUM

Suma todos los números de una columna.

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.

Función

Uso

AVERAGEX

Calcula el promedio de un conjunto de expresiones evaluado en una tabla.

COUNTAX

Cuenta el promedio de un conjunto de expresiones evaluado en una tabla.

COUNTBLANK

Cuenta el número de valores en blanco en una columna.

COUNTX

Cuenta el número total de filas de una tabla.

COUNTROWS

Cuenta el número de filas devuelto de una función de tabla anidada, como una función de filtro.

SUMX

Devuelve la suma de un conjunto de expresiones evaluadas sobre una tabla.

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.

Funciones de inteligencia de tiempo de DAX

Además de las funciones de agregación de tablas descritas en la sección anterior, DAX tiene funciones de agregación que usan las fechas y horas que se especifiquen, para proporcionar inteligencia de tiempo integrada. Estas funciones utilizan intervalos de fechas para obtener valores relacionados y agregar los valores. También puede comparar valores en los intervalos de fechas.

La siguiente tabla incorpora una lista de las funciones de inteligencia de tiempo que se pueden utilizar para la agregación.

Función

Uso

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Calcula un valor al final del calendario del período dado.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Calcula un valor al final del calendario del período antes del período dado.

TOTALMTD

TOTALYTD

TOTALQTD

Calcula un valor sobre el intervalo que se inicia el primer día del periodo y finaliza en la última fecha de la columna de fecha especificada.

Las otras funciones de la sección de funciones de inteligencia de tiempo (Funciones de inteligencia de tiempo) se pueden usar para recuperar fechas o intervalos personalizados de fechas para usar en la agregación. Por ejemplo, puede utilizar la función DATESINPERIOD para devolver un intervalo de fechas y usar ese conjunto de fechas como argumento de otra función que calcule una agregación personalizada solo para esas fechas.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×