Para este ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente.
Tenemos otro modelo, donde en vertical tenemos los centros, y, en horizontal, en una columna tenemos dos productos, y, en otra columna, tenemos tres productos.
Queremos calcular el total de ventas para cada centro, y, los dos primeros productos, y, también de los tres productos siguientes.
Lo vamos a hacer primero en Excel.
Para el primer total, el centro debe de ser igual a C.C. Nervión, y, los productos tienen que ser Aspiradora, y, Home Cinema, para los productos vamos a usar el operador lógico O, que nos devuelve VERDADERO, si una de las condiciones se cumple, lo que ocurre, es que el operador lógico O, no lo podemos usar de forma matricial, pero lo podemos hacer a través de la función BYROW.
La función BYROW, aplica una función LAMBDA a cada fila del rango de datos, y, devuelve una matriz, cuando usamos BYROW, la matriz se divide en filas, es decir, en las filas que tenga el rango de datos.
En la celda H6, voy a poner la función BYROW, el primer argumento es array, pues, seleccionamos la columna de productos.
=BYROW(Ventas3[Producto]
El siguiente argumento es función, donde tenemos que usar la función LAMBDA.
=BYROW(Ventas3[Producto];LAMBDA(
El primer argumento de la función LAMBDA, es parámetro o calculo, pues vamos a declarar una variable, esta variable, almacenara el argumento array.
=BYROW(Ventas3[Producto];LAMBDA(a
El siguiente argumento vuelve a ser parámetro o calculo, en este caso, vamos a realizar el cálculo, para ello, ponemos el operador lógico O, como primera condición es que la variable debe de ser igual al valor de la celda H3.
=BYROW(Ventas3[Producto];LAMBDA(a;O(a=H3
Y, la siguiente condición, es que, si no es igual a H3, debe de ser igual a la celda H4.
=BYROW(Ventas3[Producto];LAMBDA(a;O(a=H3;a=H4
Cerramos paréntesis del operador lógico O, de la función LAMBDA, y, de la función BYROW.
Aceptamos.
=BYROW(Ventas3[Producto];LAMBDA(a;O(a=H3;a=H4)))
Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Pero, tenemos las coincidencias de los productos, nos falta el centro, para ello, después del signo igual, voy a usar el condicional SI, donde voy a preguntar que, si el resultado de BYROW es VERDADERO, que pregunte si la columna centro de la tabla ventas es igual al primer centro.
=SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=H3;a=H4)));SI(Ventas[Centro]=G6
En ese caso, que me devuelva la columna de total, en caso contrario, que ponga un texto en blanco, para cada argumento valor si falso de cada condicional SI.
=SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=H3;a=H4)));SI(Ventas[Centro]=G6;Ventas[Total];"");"")
Aceptamos.
Obtenemos una matriz desbordada pero con los totales donde hay coincidencia, y, blanco donde no la hay.
Ahora, después del signo igual, uso la función SUMA para obtener el total, lo pongo en formato de moneda sin decimales.
=SUMA(SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=H3;a=H4)));SI(Ventas[Centro]=G6;Ventas[Total];"");""))
Tenemos el total para los productos de la primera columna, y, el primer centro comercial.
Copiar la expresión, excepto el signo igual, y, la pego en la celda I6, ahora, tenemos que añadir una nueva condición dentro del operador lógico O, pues son tres productos.
=SUMA(SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=I2;a=I3;a=I4)));SI(Ventas[Centro]=G6;Ventas[Total];"");""))
Aceptamos, y, tenemos las ventas para los tres productos, y, el primer centro comercial.
En la primera función, fijamos H3, y, H4.
=SUMA(SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=$H$3;a=$H$4)));SI(Ventas[Centro]=G6;Ventas[Total];"");""))
En la segunda función, fijamos I2, I3, e, I4.
=SUMA(SI(BYROW(Ventas[Producto];LAMBDA(a;O(a=$I$2;a=$I$3;a=$I$4)));SI(Ventas[Centro]=G6;Ventas[Total];"");""))
Ahora, arrastramos, y, tenemos el total para los dos primeros productos, y, los centros comerciales, y, por los tres siguientes productos y centros comerciales.
Ahora, lo vamos a hacer en Power Pivot, para ello, con una celda dentro del modelo, vamos a la pestaña insertar, y, hacemos clic en tabla dinámica.
Se abre la ventana de tabla dinámica, marcamos hoja de calculo existente, la ponemos debajo del modelo anterior, y, marcamos agregar estos datos a modelo de datos, quiere decir, que será cargado en Power Pivot.
Si miramos en la zona de campos, el siguiente icono, indica que esta tabla proviene de un modelo cargado en Power Pivot.
Hacemos clic sobre el nombre de la tabla con botón alternativo de ratón, y, seleccionamos agregar medida.
Se abre la ventana de medida, como nombre le voy a poner primeros productos, en la ventana de formula, voy a usar la función CALCULATE, como argumento expresión, uso la función SUM para sumar la columna de total, como argumento filtrar1, pongo la columna producto seguido de la palabra IN, es igual que el operador lógico O, abrimos unas llaves, y, ponemos los dos productos, separador por punto y coma, cerramos la llave, cerramos paréntesis de CALCULATE, lo ponemos en formato de moneda.
Aceptamos.
Bajamos a filas, la columna de producto, y, a valores la medida que acabamos de crear.
Obtenemos los mismos resultados para los dos primeros productos, que los obtenido anteriormente.
Ahora, tenemos que crear la siguiente medida, la función seria la misma que la anterior, pero dentro de IN, debemos de cambiar los productos, y, añadir un producto más, pero, si tenemos una medida que nos da el total para los dos primeros productos, quiere decir que, si resto la suma de la columna total, menos la medida, nos dará el total para los tres productos, como sigue:
Lo ponemos en formato de moneda, y, aceptamos, bajamos la medida a valores, y, vemos que tenemos los mismos resultados.
Comments