Archivos marzo 2021

¿Cómo conseguimos BI en Excel?

Las medidas DAX en una tabla dinámica son cálculos que usamos para el análisis de datos.

DAX (Data Analysis Expressions) nos proporciona inteligencia de negocio (Business Intelligence, BI) dentro del familiar entorno de Excel.

Para muchos usuarios, las numerosas funcionalidades de la tablas dinámicas son suficientes para sus necesidades. Por otra parte, los usuarios de Excel interesados en BI amplían las posibilidades del análisis de datos con DAX.

DAX es un lenguaje de fórmula. Como estamos acostumbrados a la sintaxis de fórmulas de Excel, encontraremos que la sintaxis de DAX es muy similar. La gran diferencia es que las fórmulas de Excel están basadas en celdas, mientras que las fórmulas DAX lo están en columnas.

En nuestro ejemplo, queremos saber el importe del pago de comisiones de venta por producto (un 5%) en cada ciudad.

En primer lugar, vamos a crear un modelo de datos para trabajar con una base de datos. A continuación creamos una tabla dinámica y añadimos la medida que necesitamos. Por último, damos formato a la tabla dinámica.

Modelo de datos

Un modelo de datos crea un origen de datos relacional dentro de un libro de Excel.

Está compuesto por tablas, columnas, tipos de datos y relaciones entre las tablas. Nos permite integrar datos procedentes de distintas fuentes de forma eficaz.

En Excel, el modelo de datos se muestra como una colección de tablas con su lista de campos.

Modelo de datos y tabla dinámica

Para nuestro caso, contamos con una base de datos de más de veinte y un mil registros.

Queremos un resumen de ventas por ciudad y que incluya el importe de la comisión de dichas ventas (un 5%), .

¿Cómo lo hacemos?

Lo primero es crear el modelo de datos en Excel, a partir de la base de datos. Seguidamente la tabla dinámica y, dentro de ella, la medida DAX.

Paso 1: creamos el modelo de datos

Para crear un modelo de datos, lo primero que tenemos que conseguir son los datos. Lanzamos la interfaz de Power Pivot desde el menú Power Pivot >> Administrar

La interfaz de Power Pivot se presenta como una ventana independiente.

Para cargar los datos, elegimos el menú De base de datos >>> De Access

Aparece la ventana que nos importará las tablas al modelo de datos:

Ahora conectamos con la base de datos:

Excel nos pide que seleccionemos las tablas o vistas con los datos a importar:

Cuando queremos conseguir varias tablas de un mismo origen de datos, elegimos en la lista de tablas y vistas. Al seleccionar varias tablas, Excel nos crea automáticamente un modelo de datos auqneu podremos modificarlo. En nuestro ejemplo solo tenemos una tabla:

La conexión es exitosa y hemos creado el modelo de datos correcatmente.

Power Pivot nos presenta os datos como columnas de una tabla:

Guardamos el modelo de datos. Las tablas que acabamos de importar nos aparecerán en la lista de campos de la tabla dinámica.

Paso 2: creamos la tabla dinámica

Una vez creado el modelo de datos, nos situamos en cualquier celda de la hoja de trabajo para insertar la tabla dinámica.

Elegimos menú Insertar >>> Tabla dinámica

Excel muestra la ventana >>> Crear tabla dinámica

Como los datos que queremos analizar proceden de un modelo de datos, Excel ha elegido la opción Usar el modelo de datos de este libro:

Agregamos los campos de la tabla en la tabla dinámica. Los campos PRODUCTO y CIUDADA como filas, mientras que las VENTAS serán valores:

Aceptamos la configuración y obtenemos la tabla dinámica:

Paso 3: creamos la medida

Para crear campos calculados en una tabla dinámica, usamos el menú Analizar >>> Campos, elementos y conjuntos.

Sin embargo, cuando usamos un modelo de datos esta opción nos aparece deshabilitada:

Por lo tanto necesitamos otro método para agregar una medida a la tabla dinámica. En la ventana >>> Campos de la tabla dinámica hacemos click con el botón derecho sobre la tabla de los datos (en nuestro caso, VENTAS):

Aparece la ventana >>> Medida, que nos permite crear y comprobar medidas en DAX:

Damos un nombre a la medida en la opción >>> Nombre de la medida, y escribimos la fórmula en el área para escritura de las fórmulas:

A continuación comprobamos que la fórmula no contiene errores mediante el botón de opción >>> Comprobar fórmula DAX

La medida aparece en la ventana >>> Campos de tabla dinámica:

Configuramos la tabla dinámica:

Por último, aceptamos y la tabla dinámica ya incluye la medida DAX que hemos creado:

Paso 4: damos formato a la tabla dinámica

Ahora damos formato a los valores de la tabla para ofrecer un informe de las comisones fácil de descifrar.

En primer lugar, cambiamos el nombre de los encabezados de los campos por otro con mayor significado. También los rotulamos para que destaquen:

Dentro de la tabla conseguimos el menú auxiliar de Excel (por ejemplo con el botón derecho del ratón) y elegimos el menú >> Formato de número. Damos formato moneda a los campos VENTAS y COMISIÓN:

Y tenemos listo el informe de comisiones de ventas de productos por ciudad: