En este escenario partimos de dos tablas:
- DimEmployee, conteniendo información sobre los empleados. Esta tabla contiene el campo Name con el nombre completo de cada vendedor, y el campo SalesTerritoryKey, con el territorio de ventas asignado a cada empleado. Hay 10 territorios de ventas (de 1 a 10). Los empleados que no trabajan en ventas reciben un SalesTerritoryKey de 11.
-
FactResellerSales, conteniendo información sobre las ventas realizadas. Esta tabla contiene el campo SalesAmount con el precio de venta (de cada una de las ventas) y está enlazada con la anterior a través de un campo EmployeeKey.
El objetivo es la creación de una tabla calculada en la que se muestren tres campos: el nombre de cada vendedor, su territorio de ventas y la cifra media de ventas de cada uno.
Para la creación de la tabla vamos a utilizar la función SELECTCOLUMNS a partir de la tabla de empleados, extrayendo el nombre de los empleados que nos interesan, su territorio, y añadiendo su cifra media de ventas. En pseudo-código sería:
Average sales amount per seller = SELECTCOLUMNS(
DimEmployee;
"Name"; <nombre de cada empleado de ventas>;
"Territory"; <territorio de cada empleado>;
"Average sales"; Cálculo-de-ventas-medias
)
Como primer intento, vamos a calculas las ventas medias usando la función AVERAGE para calcular el valor medio de la columna SalesAmount. El código sería el siguiente:
Average sales amount per seller =
SELECTCOLUMNS(
DimEmployee;
"Name"; DimEmployee[Name];
"Territory"; DimEmployee[SalesTerritoryKey];
"Average sales"; AVERAGE(FactResellerSales[SalesAmount])
)
Y el resultado:
Vemos dos errores: se están mostrando empleados que no pertenecen a ventas (cuyo territorio es el 11) y todas las cifras medias de ventas son iguales, señal de que no estamos personalizando el cálculo para cada vendedor. Arreglemos en primer lugar el primer problema: debemos considerar solo los empleados cuyo territorio de ventas no sea 11. En el código usado vemos que estamos partiendo de la tabla DimEmployee para extraer el nombre, etc. Podemos partir, no de esta tabla completa, sino de la tabla filtrada usando la función FILTER, de forma que solo se incluyan los vendedores:
Average sales amount per seller =
SELECTCOLUMNS(
FILTER(
DimEmployee;
DimEmployee[SalesTerritoryKey] <> 11
);
"Name"; DimEmployee[Name];
"Territory"; DimEmployee[SalesTerritoryKey];
"Average sales"; AVERAGE(FactResellerSales[SalesAmount])
)
El resultado en este caso es el siguiente:
...lo que tiene mucho mejor aspecto. Al respecto del segundo problema -que las cifras devueltas por la función AVERAGE sean las mismas, debemos entender que esta función está considerando toda la tabla de ventas para hacer el cálculo. Tenemos que filtrar esta tabla para que solo considere las ventas de cada empleado. Para ello podemos usar la función RELATEDTABLE que cambia el contexto en el que se va a realizar el cálculo filtrando la tabla de ventas de forma que solo se considere el subconjunto de esta tabla que esté relacionado con la fila (con el vendedor) siendo considerada. Es decir, para el primer vendedor que vemos en la imagen anterior, Michael Blythe, la función RELATEDTABLE filtraría la tabla de ventas extrayendo solo las ventas en las que Michael aparezca como vendedor asociado a la venta. Pero la función AVERAGE no admite una tabla como argumento... Afortunadamente podemos recurrir a la función AVERAGEX que acepta una tabla como primer argumento y una expresión como segundo (que puede ser simplemente el campo SalesAmount que nos interesa):
Average sales amount per seller =
SELECTCOLUMNS(
FILTER(
DimEmployee;
DimEmployee[SalesTerritoryKey] <> 11
);
"Name"; DimEmployee[Name];
"Territory"; DimEmployee[SalesTerritoryKey];
"Average sales"; AVERAGEX(
RELATEDTABLE(FactResellerSales);
FactResellerSales[SalesAmount])
)
Ahora el resultado es el correcto:
