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

Lógica condicional compleja en Power Query

Refiriéndose a la nota anterior me consulta un lector: si se puede hacer lo mismo utilizando select case en caso de que cada vendedor tuviera tramos diferentes de comisión.  Esta consulta puede interpretarse de dos maneras:

• que cada vendedor tenga una diferente escala de comisiones;

• que los vendedores se hicieran acreedores a una comisión del 5% por los primeros 20,000 (nuevamente, la moneda que les venga cómoda), 7.5% por los siguientes 30,000; 9% por los siguientes 50,000 y un 10% por las ventas encima de las 100,000.

Seguramente mi lector se refiere al primer caso ya que si lo quisiéramos resolver con Vba, seguramente usaríamos Select Case. Tendríamos primero que identificar al vendedor y luego aplicar "su" escala de comisiones a su total de ventas.

El segundo caso implica calcular la comisión para cada "tramo" de las ventas, algo así como el impuesto progresivo o escalonado.

En este post vamos a analizar las soluciones posibles con Power Query para el primer caso. En otra nota nos ocuparemos del segundo caso.

Cálculo condicional en Power Query

Casi todo proceso de transformación de datos incluye, en algún momento, un cálculo condicional. Este cálculo puede ser un simple si condición, entonces resultado, sino otro resultado  hasta una compleja serie de SI anidados.

En Excel Clásico disponemos de la función SI donde separamos las condiciones con comas y podemos anidar hasta 64 condiciones (aunque como ya he señalado es preferible usar BUSCARV en lugar de complicarnos la vida, que bastante complicada es aún sin nuestra colaboración).

En Power Query, como era de esperar, podemos usar la interfaz de usuario para escribir fórmulas condicionales

pero en ciertos casos no podemos apoyarnos en la interfaz y tendremos que escribir la fórmula por nuestros propios medios.
En este post voy a mostrar las dos posibilidades.