Si estamos cursando el curso de Excel básico hasta avanzado, en el video 153 perteneciente al nivel intermedio, vimos como calcular el total por producto, y, por vendedor.
Si estamos cursando Excel intermedio, lo tenemos en el video 95.
Pues, vamos a realizar lo mismo, pero en Power Pivot, donde usaremos una relación de muchos a muchos, donde vamos a tener el mismo problema, tenemos dos tablas, una de ellas con el idvendedor, y, el vendedor, y, otra con los artículos vendidos, y, el vendedor, como en el ejemplo anterior, tenemos productos que han sido vendidos por dos vendedores.
Después, tenemos la tabla con los idproducto, y, las unidades vendidas.
Tenemos que calcular el total de unidades vendidas por producto, y, vendedor.
Vamos a resolver este problema con fórmulas DAX.
Vamos a ver cómo funciona una relación de muchos a muchos.
Si nos fijamos en las tablas idvendedor/vendedor, y, coste de producto, vemos que el vendedor 102, ha vendido los productos DA22, GD14, y, BS43.
La tabla idvendedor/vendedor es el lado uno, son varios los productos que ha vendido el vendedor 102, pero, si miramos, al contrario, desde la tabla coste de producto a la tabla idvendedor/vendedor, vemos que el producto DA22, ha sido vendido por los vendedores 101, y, 102.
Quiere decir que la tabla coste de producto es una tabla única de productos, porque ninguno de ellos se repite.
Entonces, cuando tenemos una relación de uno a muchos de una tabla a otra, y, al contrario, tenemos una relación de muchos a muchos.
Estos modelos están en formato de tabla.
Vamos a llevar estas tablas a Power Pivot, con una celda dentro de la primera tabla, vamos a la pestaña de Power Pivot, y, hacemos clic en agregar a modelo de datos.
Seguimos los pasos anteriores, y, llevamos los otros dos modelos.
Ya tenemos las tres tablas en Power Pivot.
Vamos a la pestaña de inicio, dentro del grupo ver, hacemos clic en vista de diagrama.
Vemos las tres tablas.
La primera relación es fácil, va desde idproducto de la tabla producto hasta idproducto de la tabla venta_producto, donde se crea una relación de uno a muchos, donde el lado uno es la columna idproducto de la tabla venta_producto, y, el lado varios, en la columna idproducto de la tabla producto.
Si creamos una tabla dinámica, podemos las unidades vendidas por producto, para ello, volvemos a la vista de datos, vamos a la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.
Dejamos marcado nueva hoja de cálculo, y, aceptamos.
Nos llevamos producto de la tabla ventas_producto a filas, y, unidades vendidas de la tabla producto a valores.
Tenemos las unidades vendidas por producto.
Como tenemos una relación de uno a muchos, ha filtrado correctamente cada producto de la tabla ventas_producto, y, nos ha devuelto las unidades vendidas para cada producto.
Voy a copiar la tabla dinámica, y, la pego unas celdas mas abajo, para ello, selecciono la tabla dinámica, pulso CTRL mas C para copiar, selecciono la celda donde pegar, y, pulsamos CTRL más V.
Quito de filas, la columna producto, y, me llevo vendedor de la tabla vendedor.
Vemos que nos devuelve el total de unidades vendidas para cada vendedor, esto es debido porque no existe una relación.
Vamos a tener que crear una tabla intermedia, donde vamos a tener todos los idproducto repetidos para cada vendedor, después, a través de la tabla intermedia, construiremos una relación de uno a muchos.
Esto lo haremos con Power Query, entonces, con una celda dentro de la tabla venta_producto, vamos a la pestaña de datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.
Solo necesitamos las siguientes columnas.
Por lo que, dentro de una de ellas, hacemos clic con botón alternativo de ratón, y, seleccionamos quitar otras columnas.
Ahora, a cada producto, le tenemos que asignar el vendedor que ha vendido dicho producto, por lo que los productos se repetirán, para ello, teniendo seleccionada la columna de idproducto, hacemos clic con botón alternativo de ratón, y, seleccionamos anulación de dinamización de otras columnas
Vemos como a cada producto se le ha asignado los vendedores que ha vendido dicho producto.
La columna atributo, no la vamos a necesitar, por lo que hacemos clic con botón alternativo de ratón, y, seleccionamos quitar.
A la columna valor, le cambio el nombre y le pongo idvendedor.
Le cambio el nombre a la consulta, y, le pongo intermedia.
Ahora, voy a cerrar y cargar en, para ello, desde la pestaña de inicio, desplegamos cerrar y cargar, y, seleccionamos cerrar y cargar en.
Se abre la ventana importar datos, marcamos tabla, marcamos hoja de calculo existente, y, seleccionamos una celda dentro del modelo, aceptamos.
En la parte derecha, vemos la ventana de consultas y conexiones, con nuestra conexión.
Seleccionamos una celda, y, la llevamos a Power Pivot.
Si vamos a la vista de diagrama, veremos nuestra tabla intermedia.
Ahora, voy a crear las relaciones que necesitamos, para ello, llevo idvendedor de la tabla intermedia hasta idvendedor de la tabla vendedor, donde se creara una relación de uno a muchos, siendo el lado uno la tabla vendedor, y, el lado varios, la tabla intermedia.
Ahora, llevo idproducto de la tabla intermedia hasta idproducto de la tabla venta_producto, donde se crea otra relación de uno a varios, donde el lado uno es la tabla venta_producto, y, el lado varios es la tabla intermedia.
Si volvemos a la tabla dinámica que creamos donde para cada vendedor, aparecía el total, esto es porque no puede llegar a la tabla venta_producto, hasta ahora, idvendedor de la tabla vendedor llega hasta idvendedor de la tabla intermedia.
Un filtro puede pasar del lado varios al lado uno, aunque de forma predeterminada el filtro fluye del lado uno al lado varios, esto lo podíamos arreglar con un filtro bidireccional, en Power BI, podemos cambiar la dirección de una relación, volvemos a Power Pivot, donde vamos a usar CROSSFILTER, que nos permite indicar la dirección de una relación, voy a ir a la vista de datos, selecciono la tabla intermedia, voy a la zona de cálculo, donde voy a crear una medida para calcular la suma de la columna unidades vendidas de la tabla producto, donde usare la función SUM.
Unidades_totales:=SUM(Producto[Unid. Vendidas])
Ahora, voy a crear otra medida, que la llamare filtro cruzado, donde usare CROSSFILTER, es decir, voy a usar la función CALCULATE para calcular la medida unidades totales, y, con CROSSFILTER le voy a decir que idproducto de la tabla intermedia, se relacione con idproducto de la tabla producto en una relación de ambos.
Filtro cruzado:=CALCULATE(
[Unidades_totales];CROSSFILTER(Intermedia[Idproducto];Venta_producto[Idproducto];Both))
Aceptamos, y, tenemos el mismo total que para la medida unidades totales.
Vamos a volver al modelo en Excel, donde creamos la tabla dinámica por vendedor.
Voy a quitar de valores, unidades vendidas.
Y, llevo la medida filtro cruzado.
Vemos como ahora tenemos las unidades vendidas para cada vendedor.
Por último, vamos a crear una tabla dinámica, para ver las unidades vendidas por producto y vendedor, para ello, llevamos a filas producto de la tabla venta_producto, a columnas, vendedor de la tabla vendedor, y, a valores, la medida filtro cruzado.
Ya tenemos nuestro resumen.
Tenemos los mismos totales que hemos obtenido en la tabla dinámica, donde hemos resumido por vendedor.
Vemos que hay cantidades que se repiten debido a que ese producto fue vendido por dos vendedores.
Pues, ya tenemos nuestro ejercicio realizado.
Comments