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.

Para el primer caso vamos a a suponer que existen tres tipos de vendedores: A, B y C. Cada tipo de vendedor tiene una escala distinta que podemos ver en esta tabla

Los encabezados de las columna muestran el límite inferior de cada tramo (por ejemplo, los vendedores de tipo A reciben una comisión del 3% para ventas inferiores a 50 mil y una comisión del 5% si las ventas están entre 50 mil y 100 mil).

Para crear nuestro informes de comisiones a pagar tenemos que aplicar una lógica compleja: primero determinar que tipo de vendedor y luego en que tramo cae el total de ventas.
Voy a mostrar dos métodos de hacerlo. El primero con logical condicional compleja y el segundo usando combinación de consultas.

Solución con condicional compleja.

En la nota anterior vimos que podemos combinar condiciones con if, then, else if.  También podemos "anidar" condiciones con if dentro de if  (como hacemos con Excel).
En este caso vamos a utilizar el operador and (tal como conocemos AND en Excel Clásico; la diferencia es que en Power Query lo usamos en inglés y en minúsculas).

Una primera solución es crear una conexión a la tabla de vendedores,

 y luego agregar una columna con el cálculo de las comisiones usando la construcción if - then -else if  y el operador and para combinar el tipo de vendedor con el monto de las ventas. Usamos Agregar columna-Columna personalizada y ponemos esta fórmula

lo que nos agrega una columna con los porcentajes correspondientes

El próximo paso es multiplicar la columna [Ventas 2017] por la columna [Porcentaje] para lo cual seleccionamos ambas columnas y aplicamos Agregar columna-De número-Estándar-Multiplicar

obteniendo

y finalmente lo cargamos a una hoja

Personalmente no me gusta esta forma de encarar el problema. Podemos apreciar dos problemas, en mi opinión, críticos:

1. los porcentajes de las comisiones son valores fijos por lo que en cada caso que cambiemos algún valor tendremos que editar la fórmula y hacer los cambios correspondientes;
2. en este caso tenemos tres tipos de vendedores y tres tramos para cada tipo, lo que resulta en una fórmula con nueve condiciones; si tuviéramos, por ejemplo, 5 tipo de vendedores con 4 tramos nuestra fórmula tendría 20 condiciones. Con cada grado de complejidad aumentan las posibilidades de que introduzcamos algún error que sería difícil de detectar.

Mi preferencia es usar una tabla "tipo/porcentaje" y usar combinación de consultas. 

Solución con combinación de consultas.

El video con todo el proceso aparece al final de la nota.

Empezamos por cargar la tabla "tipo/comisión" que vimos más arriba a una consulta

Para poder combinar esta tabla con la de las ventas vamos a proceder a "aplanarla" (unpivot) seleccionando la columna "Tipo" y aplicando Anular dinamización de columnas-de otras columnas

Cambiamos el encabezamiento "Atributo" a "Límite inferior" y "Valor" a "Porcentaje". Además cambiamos el tipo de datos de "Atributo" a "número decimal"

Cerramos la consulta como "sólo conexión". Ahora editamos la conexión a la tabla de ventas y agregamos una columna condicional para identificar el tramo en que caen las ventas en cada fila. Podemos hacerlo usando Agregar columna-Columna condicional

o escribiendo la fórmula en el editor de Columna personalizada

=if [Ventas 2017]>=100000 then 100000else if [Ventas 2017]>=50000 then 50000else 0

Ahora creamos una nueva consulta que combinará la tabla de ventas con la de los tramos

Nótese que usamos dos columnas en cada tabla para realizar la combinación. Esto nos permite identificar primero el "Tipo" de vendedor y luego el tramo.Expandimos la tabla de tramos sólo con la columna "Porcentaje"

Solo nos queda por multiplicar las columnas [Ventas 2017] con [Porcentaje], eliminar las columnas que no queramos que aparezcan en el reporte y cargar la consulta a una hoja.

La ventaja de este método sobre el anterior es la facilidad con la que podemos mantener los datos (no hace falta editar la fórmula) y el control sobre la exactitud de los datos.

Este video muestra todo el proceso.