Crear una medida en una tabla dinámica o gráfico dinámico

  • Artículo

Una medida es un cálculo que se crea con el fin de medir un resultado con respecto a otros factores que son pertinentes para el análisis, como el tiempo, la geografía, la organización o las características del producto. Hay varias maneras de crear una medida pero, dependiendo de cómo piense utilizar la medida, no todos los enfoques son igualmente efectivos. Concretamente, si está creando un libro para usarlo como modelo de datos en una aplicación de informes, debe aseguarse de crear solo medidas explícitas, como se describe en las secciones siguientes.

Crear medidas para usarlas en modelos de datos

Las medidas que cree en un libro PowerPivot se pueden utilizar en los informes que cree en Power View y otras aplicaciones de informes, coexistiendo con otras medidas o cálculos cree directamente en el informe. Para que una medida aparezca en el informe como cálculo predefinido, deberá crearla en un libro PowerPivot como medida explícita. Una medida explícita es aquella que se crea manualmente. Contrasta con las medidas implícitas que Excel genera automáticamente al agregar campos al área Valores de una tabla dinámica.

Crear medidas para usarlas en Excel

Cuando cree una medida para usarla en Excel, debe agregar primero una tabla dinámica o un gráfico dinámico a su libro PowerPivot. A continuación, puede crear medidas mediante cualquiera de los enfoques siguientes:

  • Cree una medida implícita arrastrando un campo de la lista de campos de PowerPivot al área Valores. Si arrastra un campo numérico, la medida implícita se calcula utilizando una agregación SUM. Si arrastra un campo de texto, la medida se calcula utilizando una agregación COUNT. Puede editar una medida implícita para cambiar el cálculo a una agregación diferente, como MIN, MAX o DISTINCTCOUNT.

    [!NOTA]

    Las medidas implícitas son fáciles de crear, pero tienen más limitaciones que las explícitas. Las medidas implícitas no se pueden cambiar de nombre, mover o utilizar en otras tablas dinámicas o gráficos del libro. Además, como las medidas implícitas se basan en un campo existente, la eliminación de ese campo también elimina las medidas implícitas relacionadas. Finalmente, las medidas implícitas solo pueden utilizar el formato de datos que se integra en la agregación; no admiten la amplia gama de formatos de datos que están disponibles para las medidas explícitas.

  • Cree una medida explícita manualmente mediante el botón de Nueva medida en la cinta de opciones de PowerPivot.

  • Cree una medida explícita manualmente en la ventana de PowerPivot, en el Área de cálculo escribiendo un nombre y una fórmula de la medida en el área de fórmula de una celda.

Al agregar la medida, la fórmula se evalúa para cada celda del área Valores de la tabla dinámica. Puesto que se crea un resultado para cada combinación de encabezados de fila y columna, el resultado para la medida puede ser diferente en cada celda de la tabla dinámica.

Ejemplo: crear una medida explícita que usa una agregación simple

Este ejemplo incluye los datos relacionados con bicicletas de la base de datos de AdventureWorks. Para obtener más información acerca de dónde obtener el libro de ejemplo, vea Obtener datos de muestra para PowerPivot. Para obtener más información acerca de las fórmulas, vea Crear fórmulas para cálculos.

En este ejemplo se muestran dos maneras de crear una medida explícita. En primer lugar, creará una medida en la ventana de PowerPivot, en un área de cálculo que muestra todas las medidas definidas en el modelo. En segundo lugar agregará una tabla dinámica o un gráfico dinámico a su libro de PowerPivot y, a continuación, usará el cuadro de diálogo Configuración de medida para agregar una medida. La fórmula de una medida define una suma, un promedio u otro cálculo utilizando las columnas y las tablas del libro PowerPivot.

  1. En la ventana de PowerPivot, haga clic en la pestaña Inicio y, en el grupo Vistas haga clic en Área de cálculo.

  2. En la tabla FactResellerSales, haga clic en una celda de cualquier lugar del Área de cálculo.

  3. En la barra de fórmulas situada en la parte superior del libro, escriba una fórmula en este formato <nombreDeMedida>:<fórmula>:

    Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06

  4. Haga clic en Aceptar para aceptar la fórmula.

  5. En la ventana de PowerPivot, haga clic en la pestaña Inicio y en el grupo Informes haga clic en Tabla dinámica.

    En el cuadro de diálogo Crear tabla dinámica, compruebe que Nueva hoja de cálculo está seleccionada y haga clic en Aceptar.

    PowerPivot crea una tabla dinámica en blanco en una nueva hoja de cálculo de Excel y muestra Lista de campos de PowerPivot en el lado derecho del libro.

  6. Expanda la tabla FactResellerSales para ver la medida que acaba de crear. Si ya tuviera una tabla dinámica en el libro antes de crear la medida, debe hacer clic en el botón Actualizar de la parte superior de la lista de campos de PowerPivot para actualizar los campos.

  7. En la ventana de Excel, en le pestaña PowerPivot, en el grupo Medidas, haga clic en Nueva medida.

  8. En el cuadro de diálogo Configuración de medida, para Nombre de la tabla, haga clic en la flecha abajo y seleccione FactResellerSales en la lista desplegable.

    La elección que haga en la tabla determina el lugar en el que se almacenará la definición de la medida. No es necesario que la medida se almacene con una tabla a la que haga referencia.

  9. En Nombre de medida (todas las tablas dinámicas), escriba Total Quantity.

  10. El nombre de la medida debe ser único dentro de un libro y no puede utilizar el mismo nombre que se use para alguna de sus columnas.

  11. En el cuadro de texto Fórmula, coloque el cursor a continuación del signo igual (=) y escriba la fórmula siguiente:

    SUM(FactResellerSales[OrderQuantity])

  12. Haga clic en Aceptar.

Las dos medidas creadas se guardarán con la tabla de datos de origen, pero pueden ser utilizada por cualquier tabla dinámica o gráfico dinámico. Las medidas aparecen en la Lista de campos de PowerPivot y están disponibles para todos los usuarios del libro.

Ejemplo: crear una medida explícita que usa una agregación personalizada

En este ejemplo, creará una agregación personalizada que use una de las nuevas funciones de agregación de DAX, la función SUMX, y la función ALL, que en este caso devuelve todos los valores de una columna sin tener en cuenta el contexto de esa columna. En el ejemplo se utilizan las siguientes columnas del libro de ejemplo de DAX:

  • DateTime[CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD[SalesAmount_USD]

En el ejemplo se utiliza una tabla dinámica que tiene CalendarYear como un etiqueta de fila y ProductCategoryName como etiqueta de columna; SalesAmount_USD se utiliza en la fórmula de la medida. En el ejemplo se responde a la pregunta: ¿qué porcentaje de las ventas totales de 2005 a 2008 se aportó por año y categoría de producto? Por ejemplo, esto le permite ver qué porcentaje del total fue aportado por las ventas de bicicletas en 2007. Para responder a esta pregunta, utilizamos la siguiente fórmula de medida:

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

La fórmula se construye del siguiente modo:

  1. El numerador, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), es la suma de los valores de ResellerSales_USD[SalesAmount_USD] para la celda actual de la tabla dinámica. Tener el contexto de CalendarYeary ProductCategoryName significa que este valor será diferente para cada combinación de año y categoría de producto. Por ejemplo, el número total de bicicletas vendido en 2003 es diferente de los accesorios de número totales vendidos en 2008.

  2. Para el denominador, se comienza por especificar una tabla, ResellerSales_USD, y se usa la función ALL para quitar todo el contexto de la tabla. De este modo se asegura de que el valor será el mismo para cada combinación de año y categoría de producto: el denominador siempre serán las ventas totales de 2005 a 2008.

  3. A continuación, se usa la función SUMX que sume los valores de la columna ResellerSales_USD[SalesAmount_USD]. En otras palabras, obtiene la suma de ResellerSales_USD[SalesAmount_USD] para las ventas de todos los distribuidores.

[!NOTA]

En Windows Vista y Windows 7, las características de la ventana de PowerPivot están disponibles en una cinta, que se explica en este tema. En Windows XP, las características están disponibles de un conjunto de menús. Si usa Windows XP y desea ver cómo se relacionan los comandos de menú con los comandos de la cinta, vea Interfaz de usuario de PowerPivot en Windows XP.

Para crear una medida que utiliza una agregación personalizada

  1. En la ventana de PowerPivot, haga clic en la pestaña Inicio y en el grupo Informes haga clic en Tabla dinámica.

  2. En el cuadro de diálogo Crear tabla dinámica, compruebe que Nueva hoja de cálculo está seleccionada y haga clic en Aceptar.

    PowerPivot crea una tabla dinámica en blanco en una nueva hoja de cálculo de Excel y muestra Lista de campos de PowerPivot en el lado derecho del libro.

  3. En la ventana de Excel, utilice la Lista de campos de PowerPivot para agregar columnas a la tabla dinámica:

    1. Busque la tabla DateTime y arrastre la columna CalendarYear al área Etiquetas de fila de la tabla dinámica.

    2. Busque la tabla ProductCategory y arrastre la columna ProductCategoryName al área Etiquetas de fila de la tabla dinámica.

  4. En la ventana de Excel, en le pestaña PowerPivot, en el grupo Medidas, haga clic en Nueva medida.

  5. En el cuadro de diálogo Configuración de medida, para Nombre de la tabla, haga clic en la flecha abajo y seleccione ResellerSales_USD en la lista desplegable.

    La elección que haga en la tabla determina el lugar en el que se almacenará la definición de la medida. No es necesario que la medida se almacene con una tabla a la que haga referencia.

  6. En Nombre de medida (todas las tablas dinámicas), escriba AllResSalesRatio.

    Este nombre se usa como identificador de la medida, por lo que debe ser único en el libro y no se puede cambiar.

  7. En Nombre personalizado (esta tabla dinámica), escriba All Reseller Sales Ratio.

    Este nombre solo se usa dentro de la tabla dinámica con fines de visualización. Por ejemplo, podría reutilizar la medida, AllResSalesRatio, en otras tablas dinámicas pero con un nombre diferente, o usar un idioma distinto.

  8. En el cuadro de texto Fórmula, coloque el cursor después del signo igual (=).

  9. Escriba SUMX y a continuación un paréntesis.

    =SUMX(

    A medida que escribe, la información sobre herramientas situada debajo del cuadro de texto Formula indica que la función SUMX necesita dos argumentos: el primero es una tabla o una expresión que devuelve una tabla y el segundo es una expresión que proporciona los números que se pueden sumar.

    Escriba Res, seleccione ResellerSales_USD en la lista y presione TAB.

    El nombre de la columna se incrusta en la fórmula del siguiente modo:

    =SUMX(ResellerSales_USD

  10. Escriba una coma.

    La información sobre herramientas se actualiza para mostrar que el siguiente argumento necesario es una expresión. Una expresión puede ser un valor, una referencia a una columna o una combinación de ambos. Por ejemplo, puede crear una expresión que sume dos columnas. Para obtener este ejemplo, proporcionará el nombre de una columna que contiene la cantidad de ventas de cada revendedor.

  11. Escriba las primeras letras del nombre de la tabla que contiene la columna que desea incluir. En este ejemplo, escriba Res y seleccione la columna ResellerSales_USD[DiscountAmount_USD] en la lista.

  12. Presione TAB para incrustar el nombre de columna en la fórmula y agregue un paréntesis de cierre, como se muestra a continuación:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])

  13. Escriba una barra diagonal y, a continuación, escriba o copie y pegue el siguiente código en el cuadro de diálogo Configuración de medida:

    SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

    Observe cómo se anida la función ALL dentro de la función SUMX. La fórmula completa aparece ahora como sigue:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

  14. Haga clic en Comprobar fórmula.

    La fórmula se comprueba por si existen errores de sintaxis o referencia. Resuelva cualquier error que se pudiera haber encontrado, como la falta de un paréntesis o coma.

  15. Haga clic en Aceptar.

    La medida rellena ahora la tabla dinámica con valores para cada combinación de año natural y categoría de producto.

  16. Dé formato a la tabla:

    1. Seleccione los datos en la tabla dinámica, incluida la fila Gran Total.

    2. En la pestaña Inicio, en el grupo Número, haga clic una vez en el botón de porcentaje (%) y, a continuación, haga clic dos veces en el botón de aumentar decimales (<-.0.00).

    La tabla terminada debería parecerse a la siguiente. Ahora puede ver el porcentaje de ventas totales de cada combinación de producto y año. Por ejemplo, las ventas de bicicletas de 2007 contabilizaban el 31,71% de todas las ventas de 2005 a 2008.

All Reseller Sales

Etiquetas de columna

 

 

 

 

Etiquetas de fila

Accessories

Bikes

Clothing

Components

Grand Total

2005

0.02%

9.10%

0.04%

0.75%

9.91%

2006

0.11%

24.71%

0.60%

4.48%

29.90%

2007

0.36%

31.71%

1.07%

6.79%

39.93%

2008

0.20%

16.95%

0.48%

2.63%

20.26%

Grand Total

0.70%

82.47%

2.18%

14.65%

100.00%

Vea también

Referencia

Cuadro de diálogo Indicador clave de rendimiento

Conceptos

Medidas en PowerPivot

Ventana de PowerPivot: Área de cálculo

Otros recursos

Información general sobre informes, gráficos y tablas dinámicas