Cálculo de comisiones por tramos

En este post vamos a tratar el segundo caso que mencioné en el anterior sobre lógica condicional con Power Query. La misión es calcular las comisiones que les corresponden a un equipo de vendedores. Estas comisiones no se calculan por el total vendido sino por tramos.
En nuestro ejemplo los vendedores se hacer acreedores a una comisión del 5% por los primeros 20,000 (digamos para el caso "pesos"), 7.5% para el tramo que va de los 20,000 a 50,000; 9% para el tramo de 50,000 a 100,000 y un 10% para el tramo por encima de los 100,000.
De esta manera por ventas de 60,000 pesos el vendedor recibirá 4,150 pesos de comisión

Vamos a resolver este cálculo con Power Query usando solamente la interfaz de usuario y con algunas fórmulas condicionales.

Están invitados a descargar el archivo para seguir los pasos del ejercicio.

Como siempre empezamos por crear una conexión a la tabla de ventas

Guardamos la consulta como "solo conexión".
Creamos una tabla con los tramos de ventas para el cálculo de las comisiones y, también, la guardamos como "solo conexión"

Antes de poder calcular las comisiones tenemos que "desgranar" cada total de ventas en sus distintos tramos. En el caso de Nancy Davolio, por ejemplo, es: 20,000 + 30,000 + 43,148.04.

Para poder dividir cada suma de ventas según los distintos tramos vamos a empezar por hacer una transformación en la tabla de comisiones. Empezamos por crear una referencia a la consulta de las comisiones

A diferencia de Duplicar, Referencia  crea una consulta cuyo origen es el resultado de la consulta referente. Abrimos la consulta "tbl_Tramos (2)", eliminamos la columna [Comisión], transponemos la tabla usando Transformar - Transponer e invertimos las filas con Transformar-Invertir filas

Guardamos la consulta, nuevamente como "solo conexión".

Ahora vamos a hacer algo un tanto inesperado;  vamos a anexar esta última tabla a la tabla de ventas (no combinar, anexar). En general anexamos tablas que tienen la misma estructura, las mismas columnas. Pero en este caso el "error" que vamos a cometer nos ayudará a resolver nuestra tarea.

Anexamos las tablas como nueva consulta

con este resultado

Al anexar agregamos ala tabla de ventas las cuatro columnas de la tabla "invertida" de comisiones.
Ahora invertimos las filas de la consulta y aplicamos Inicio-Usar la primera fila como encabezado 

Seleccionamos las columnas [Tramo 1]...[Tramo 4] y aplicamos Rellenar-Abajo 

Eliminamos la primera fila y cambiamos los encabezados de "Columna1" por "Vendedor" y "Columna2" por "Ventas"

Para dividir cada fila de ventas en sus tramos vamos a restar [Tramo 1]  de [Ventas]; luego [Tramo 2] del resultado de paso anterior y así sucesivamente hasta llegar a cero o el resto superior a los 100,000. Para ésto vamos a agregar columnas personalizadas usando expresiones condicionales.

La primer columna será

Nótese que usamos como encabezado "Tr1" para evitar conflicto de nombre con la columna "Tramo 1" existente. Más adelante cambiaremos los encabezados de las columnas calculadas.

Para el cálculo de los próximos tramos ya no podemos usar la Agregar columna-Columna condicional así que usamos el editor de Columna Personalizada. Para el segundo tramo usamos

[Ventas]-[Tramo 1] calcula el saldo que queda para el segundo tramo. Para calcular los dos restantes tramos comparamos el "saldo" con el valor del tramo; para el Tramo 3

y para el tramo 4

Eliminamos las columnas [Tramo 1]...[Tramo 4], cambiamos el tipo de dato en  las columnas [Tr1]..[Tr4]  a número decimal y reemplazamos los encabezados de las columnas [Tr1]...[Tr4] por [Tramo 1]...[Tramo 4]

Como podemos ver hemos dividido las ventas en sus componentes por tramos. Para calcular las comisiones tenemos que combinar cada suma con el porcentaje adecuado. Para poder hacerlo vamos a tener que "aplanar" las columnas [Tramo 1]...[Tramo 4] de esta tabla con Anulación de dinamización de columnas

con este resultado

Ahora podemos combinar con facilidad esta tabla con la tabla de comisiones tbl_Tramos; el campo en común es [Tramo]

Expandimos la tabla tbl_tramos tomando sólo el campo [Comisión] 

Agregamos la columna [Comisión a pagar] multiplicando [Valor] por [Comisión] 

Guardamos esta consulta como "solo conexión".
De hecho hemos concluido la tarea. A partir de esta tabla podemos crear todos los reportes que nos sean requeridos. Por ejemplo, podemos usar Agrupar por

con este resultado

Pero conociendo como conocemos  nuestros jefes, necesitamos reportes más detallados. Por eso vamos a dejar la consulta (antes de agrupar) como base de datos para crear reportes con tablas dinámicas. Podemos cargar la consulta en una hoja del cuaderno Excel o dejarla como "solo conexión" y también cargarla al modelo de datos (sobre el modelo de datos publicaré más en el futuro).

Podemos, por ejemplo crear estos reportes dinámicos:

Detalle de las ventas por tramos

Detalle de las comisiones por tramos