En el módulo de fórmulas y funciones matriciales, en el apartado de ejemplos, en los capítulos 87, cálculo de total menos descuento, y, capitulo 88, calcular conste de envío, vimos como teniendo dos tablas, calculamos el importe total, y, los costes de envío.
Si estamos cursando el curso de Excel básico hasta avanzado, son los capítulos 291, y, 292.
Y si cursamos el curso de Excel avanzado, son los capítulos 148, y, 149.
Pues, en este video, vamos a realizar lo mismo, pero en Power Pivot, donde crearemos columnas calculadas.
Tenemos las mismas tablas, pero en este caso, con la columna de envío, y, la tabla pesos incluida.
Estos modelos están en formato de tabla.
Igual que antes, vemos en la tabla ventas1, que el descuento, y, el envío es el total para cada factura, mientras que en la tabla ventas2, es a nivel de fila, es decir, que el numero de factura se puede repetir, por lo que debemos de asignar cada descuento, y, envío a cada línea de la tabla ventas2.
En este caso, en vez de usar la función BUSCARV, vamos a usar relaciones.
Tenemos la tabla ventas1, donde el numero de factura es único, no se repite, quiere decir que es la tabla de hechos, es decir, el lado uno, y, en la tabla ventas2, los números de facturas si se repiten por lo que es la tabla transaccional, o sea, el lado varios.
Después, tenemos la tabla de pesos, donde los productos son únicos, y, la tabla ventas2, donde los productos se repiten, entonces, la tabla de pesos es la tabla de hechos, y, la tabla ventas2, es la tabla transaccional.
Debemos de buscar un elemento en un lado, o, tabla relacionada, un elemento y que nos devuelva otro elemento de otra tabla.
Pues, vamos a llevarnos estas tablas a Power Pivot, para ello, 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.
Ya tenemos el primer modelo en Power Pivot.
Hacemos clic en el siguiente icono, para volver a Excel.
Con una celda dentro de la tabla ventas2, seguimos el paso anterior, para llevarlo a Power Pivot.
Volvemos a Excel, y, nos traemos la tabla de pesos.
Ya tenemos las tres tablas en Power Pivot.
Lo siguiente es crear las relaciones, para ello, dentro del grupo ver, hacemos clic en vista de diagrama.
Donde vemos las tres tablas.
Vamos a relacionar ventas1 con ventas2, en la tabla ventas1, es donde el numero de factura es único, y, en la tabla ventas2, es donde se repitan, pues, con el ratón, desde la tabla ventas1, arrastramos el campo numero de factura, a numero de factura de la tabla ventas2.
Vemos que se crea una relación de uno a muchos, siendo el lado 1, numero de factura de la tabla ventas1, donde no se repiten, y, el lado muchos, es numero de factura de la tabla ventas2, donde si se repiten.
Ahora, vamos a relacionar producto de la tabla ventas2 con producto de la tabla pesos.
En este caso, el lado 1 es la tabla pesos, porque los productos no se repiten y, el lado varios, en la tabla ventas2, donde si se repiten.
Volvemos a la vista de datos.
Vamos a la tabla ventas1, vamos a comenzar con el descuento, vamos a crear dos columnas calculadas, donde calcularemos el total, y, el porcentaje de descuento, después, calcularemos el descuento para cada línea de la tabla ventas2.
A la primera columna calculada, la voy a llamar Total ventas.
Hacemos clic en la barra de fórmulas, para obtener el total, debo de multiplicar cantidad por precio, en los videos anteriores, usamos la función SUMAPRODUCTO, aquí, vamos a usar la función SUMX, que es un iterador, quiere decir que va a iterar en cada fila, pues, en la barra de fórmulas, ponemos:
=SUMX(
El primer argumento es tabla, pero, las columnas que necesitamos se encuentran en la tabla ventas2, para ir a dicha tabla, vamos a usar la función RELATEDTABLE, que nos permite ir a una columna de una tabla relacionada.
=SUMX(RELATEDTABLE(
Ahora, debemos de indicar con que tabla vamos a trabajar, que va a ser la tabla ventas2, y, cerramos paréntesis.
=SUMX(RELATEDTABLE(Ventas2)
Este es el argumento tabla de la función SUMX.
Hasta este punto nos va a traer cada número de factura.
El siguiente argumento es expresión, que va a ser la multiplicación de cantidad por precio.
Cerramos paréntesis y aceptamos.
=SUMX(RELATEDTABLE(Ventas2);Ventas2[Cantidad]*Ventas2[Precio])
Tenemos el total para cada número de factura.
Ya tenemos total y descuento, por lo que ya podemos calcular nuestro porcentaje en decimal en una nueva columna calculada, la cual llamare porcentaje descuento, tenemos que dividir el descuento entre el total, tenemos una función llamada DIVIDE, que realiza una división, el primer argumento de esta función es numerador, que va a ser la columna de descuento.
=DIVIDE(Ventas1[Descuento]
El segundo argumento es denominador, que va a ser la columna de total ventas.
Cerramos paréntesis, y, aceptamos.
=DIVIDE(Ventas1[Descuento];Ventas1[Total ventas])
Ya tenemos nuestra tasa decimal de descuento para cada factura, ha sabido tomar el total para cada numero de factura, y, calcular la cantidad correcta.
Lo siguiente es aplicar estos porcentajes a cada numero de factura de la tabla ventas2.
Como el modelo es el mismo que para los ejemplos anteriores, recordar, que en la tabla ventas1, tenemos el numero de factura 125447 en una sola línea, porque es un registro único, pero en la tabla ventas2, lo tenemos en tres líneas.
Voy a la tabla ventas2, añado una nueva columna calculada, que la llamare descuento por producto.
Antes hemos usado la función RELATEDTABLE, porque partíamos del lado uno, en este caso, vamos a partir del lado muchos, entonces, tenemos que usar RELATED, porque tenemos que acceder a la tabla ventas1, para obtener el porcentaje de descuento.
Vamos a la barra de fórmulas, y, ponemos:
=RELATED(
Ahora, tenemos que elegir con que columna vamos a trabajar de la tabla ventas1, en este caso, es la columna de porcentajes que hemos creado.
Cerramos paréntesis y aceptamos.
=RELATED(Ventas1[Porcentaje descuento])
Obtenemos el porcentaje que corresponde con cada numero de factura, podemos ver que a las tres primeras líneas, que son el mismo numero de factura, le corresponde el mismo porcentaje.
Ahora, multiplico por cantidad y precio.
=RELATED(Ventas1[Porcentaje descuento])*Ventas2[Cantidad]*Ventas2[Precio]
Tenemos el total menos el descuento.
Le voy a poner dos decimales, para ello, vamos a la pestaña de inicio, desplegamos tipos de datos, y, seleccionamos decimal.
Hacemos clic en el siguiente icono, y, o dejamos en dos.
Si sumo las tres primeras facturas.
Será el total para la primera factura, de la tabla ventas1, aquí lo que tenemos es la proporción que le corresponde a cada línea, pero, vamos a hacerlo, para ello, vamos en la parte inferior, en una celda, escribimos el nombre para la medida, ponemos dos puntos (:), seguido del signo igual (=).
Fac_125447:=
Voy a usar la función CALCULATE, la cual nos va a sumar la columna que le indiquemos por el criterio que especifiquemos, para ello, escribo:
Fac_125447:=CALCULATE(
El primer argumento es expresión, pues, vamos a sumar la columna de descuento por producto, para ello, usaremos la función SUM.
Fac_125447:=CALCULATE(
SUM([Descuento por producto]);
El siguiente argumento es filtrar1, porque podemos poner mas de una condición, pues la condición es que la columna numero de factura de ventas2, debe de ser igual a 125447.
Cerramos paréntesis, y, aceptamos.
Fac_125447:=CALCULATE(
SUM([Descuento por producto]);
Ventas2[Numero de factura]=125447)
Vemos que tenemos el total que tenemos en la tabla ventas1 para el numero de factura 125447.
Lo siguiente va a ser crear una medida para que nos de la suma de la columna descuento por producto, para ello, debajo de donde hemos calculado el total para la factura numero 125447, escribimos el nombre para la medida, seguido de dos puntos, y, el signo igual.
Ponemos la función SUM, y, seleccionamos la columna de descuento por producto.
Total:=SUM([Descuento por producto])
A ambas medidas, le voy a dar formato de moneda, para ello, desde la pestaña de inicio, dentro del grupo formato, desplegamos:
Y, seleccionamos nuestra moneda.
Lo siguiente es calcular el envío, por lo que voy a necesitar el peso de cada producto, para calcular el peso total para cada factura.
Debemos de multiplicar la cantidad de cada producto por su peso, con eso, sabremos el total para cada producto, igual que antes, en la tabla ventas1, tendremos un peso total para cada factura, y, en la tabla ventas2, el peso será acorde a las unidades de cada producto que pertenezca a la misma factura.
Tenemos que recorrer ambas relaciones, porque si necesitamos el peso total en la tabla ventas1, vamos a tener que usar la relación entre ventas1 y ventas2, para acceder a cada numero de factura, y cantidad, ya vimos que para la primera factura, tenemos tres líneas en la tabla ventas con cantidades diferentes, y, después tenemos que pasar por la segunda relación, para obtener el peso para cada producto.
Vamos a la tabla ventas1, añadimos una nueva columna calculada, la llamare peso total.
De nuevo, voy a usar el iterador SUMX, junto con RELATEDTABLE, y, selecciono la tabla ventas1, cerrando el paréntesis de RELATEDTABLE.
=SUMX(RELATEDTABLE(Ventas2)
Con esto, accedemos a cada línea de cada número de factura.
Lo siguiente es multiplicar la cantidad.
=SUMX(RELATEDTABLE(Ventas2);Ventas2[Cantidad]
Por el peso que corresponda con cada producto, para ello, tenemos que acceder a la tabla pesos, por lo que usare RELATED, porque vamos a acceder al lado uno de la relación, y, selecciono la columna de pesos, con esto conseguimos multiplicar la cantidad por el peso del producto de cada línea.
Cerramos paréntesis y aceptamos.
=SUMX(RELATEDTABLE(Ventas2);Ventas2[Cantidad]*RELATED(Pesos[Peso]))
Obtenemos el peso total para cada factura.
Como hemos usado el iterador SUMX, y, para la factura 125447, hay tres líneas en la tabla ventas2, nos da la suma de dichas líneas.
Quiere decir que hacerlo en Power Pivot, es mas eficiente que hacerlo en Excel.
Lo siguiente es calcular la proporción que corresponde a cada línea de la tabla ventas2, el peso total, siguiente con la factura 125447, el peso total es de 625, pero en la tabla ventas2, tenemos tres líneas con el numero de factura 125447, quiere decir que dependiendo de la cantidad, le corresponderá una tasa decimal diferente.
Volvemos a la tabla ventas2, y, añadimos una nueva columna calculada, la llamare peso por producto.
Lo primero es encontrar el peso total para cada numero de factura, para ello, voy a usar la función RELATED, y, selecciono la columna peso total.
=RELATED(Ventas1[Peso total])
Obtenemos el peso para cada numero de factura, pero vemos que tenemos tres líneas para el numero de factura 125447,y, aparece el peso total para cada una de ellas.
Debemos de calcular la proporción que le corresponde a cada línea, tenemos que multiplicar la cantidad por su peso, para obtener el peso de forma individual, la operación a realizar es multiplicar la cantidad por el peso que le corresponda, y, el resultado dividirlo por el peso total.
En la barra de formula, selecciono la columna cantidad.
=Ventas2[Cantidad]
Multiplico, donde uso la función RELATED, para acceder a la tabla pesos, y, traerme el peso para cada producto.
=Ventas2[Cantidad]*RELATED(Pesos[Peso])
Con esto, multiplico el peso de cada producto por su cantidad, y, este resultado lo tengo que dividir por el peso total para cada número de factura, donde uso de nuevo RELATED, para traerme el peso total de la tabla ventas1.
Cierro paréntesis y acepto.
=Ventas2[Cantidad]*RELATED(Pesos[Peso])/RELATED(Ventas1[Peso total])
Obtenemos la proporción que le corresponde a cada producto.
Donde el numero de factura es único, aparece el valor 1, es decir, el 100%.
Lo siguiente es multiplicar esta tasa decimal por el peso total.
=Ventas2[Cantidad]*RELATED(Pesos[Peso])/RELATED(Ventas1[Peso total])*RELATED(Ventas1[Peso total])
Ya tenemos la proporción que corresponde a cada línea.
Si vamos a la tabla ventas1, vemos que el total para la factura 125447 es de 625, y, en la tabla ventas2, vemos ese total pero desglosado para cada línea, según la cantidad.
Igual que antes con la función CALCULATE, voy a calcular la suma de estas tres cantidades, que debe de ser de 625.
Peso 125447:=CALCULATE(
SUM(Ventas2[Gastos envio producto]);
Ventas2[Numero de factura]=125447)
Lo que quiero ahora es cargar los modelos, pero no como una tabla dinámica, aunque podríamos, sino como modelos de datos, para ello, vamos a ir a la pestaña de datos, dentro de obtener y transformar, hacemos clic en conexiones existentes.
Se abre la ventana de conexiones existentes, donde vemos dos pestañas, conexiones y tablas.
Hacemos clic en tablas, y, vemos nuestras tres tablas.
Seleccionamos la tabla ventas1, y, hacemos clic en abrir.
Se abre la ventana de importar datos, seleccionamos tabla, seleccionamos hoja de calculo existente, y, seleccionamos la celda donde colocarla, hacemos clic en aceptar.
Seguimos los pasos anteriores, y, nos traemos las tablas ventas2, y, pesos.
Ya tenemos nuestras tres tablas en nuestra hoja de cálculo.
Por último, vamos a crear una tabla dinámica, para ello, volvemos a Power Pivot, para ello, vamos a la pestaña de Power Pivot, y, hacemos clic en administrar.
En la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.
Se abre la ventana de crear tabla dinámica, dejamos marcado nueva hoja de cálculo, y, aceptamos
En la ventana campos de tabla dinámica, vemos nuestras tres tablas.
De la tabla pesos, voy a llevar producto a filas, y, de la tabla ventas2, llevo descuento por producto, y, peso por producto, a valores.
Obtengo el total menos el descuento, y, el peso por producto.
Comments