Publicada junio 12, 2014 
Tengo mucho tiempo sin plantear un truco de Power Pivot y DAX! veamos uno que seguramente te va a interesar.
El Caso
Digamos que tenemos un grupo de vendedores y, para incentivarlos, decidimos poner en marcha un plan de bonos por el total de sus ventas. Entre más venden, ¡más alta será la recompensa o bono!
El caso es que quiero que sea dinámico, que el usuario que toma la decisión pueda actualizar la tabla de bonos y que se calcule dependiendo del rango de fechas que se haya seleccionado.
Comencemos!
Las Tablas
Para este ejemplo he simplificado un poco las cosas y he tomado solamente las tablas que necesitamos y los campos necesarios. Comencemos con la Tabla de Ventas que luce como la siguiente:
| Fecha | Vendedor | Venta |
| 14-Mar-14 | 6 | $ 1,190.71 |
| 8-Apr-14 | 5 | $ 407.53 |
| 30-Nov-13 | 3 | $ 1,844.96 |
| 11-Mar-14 | 4 | $ 1,645.72 |
| 9-Jun-14 | 4 | $ 6,127.99 |
| 15-May-14 | 3 | $ 3,276.54 |
| 5-Dec-13 | 6 | $ 7,660.05 |
| 28-Jan-14 | 6 | $ 152.60 |
| 29-Jan-14 | 1 | $ 752.31 |
| 12-Mar-14 | 1 | $ 1,877.97 |
Mientras que la tabla de bonos luce así:
| Venta | Bono |
| 0 | 0% |
| 14000 | 20% |
| 25000 | 40% |
| 40000 | 55% |
De acuerdo a como está planteada nuestra tabla de Bonos, a mayor suma de ventas, mayor será el bono que se lleva el vendedor.
Muy importante tomar en cuenta que no hay relaciones entre estas dos tablas. Ellas dos se encuentran dentro de mi modelo de datos (Power Pivot) y ya con esto puedo pasar a…
La solución
Es necesario que el calculo sea un campo calculado y no una columna calculada pues la columna calculada sería un valor estático que agruparía a nivel de fecha y queremos que el usuario seleccione cómo quiere hacer estas dicho análisis seleccionando las fechas que desea que se tomen en cuenta para calcular el bono obtenido.
De acuerdo a esto, podemos escribir el siguiente campo calculado:
Medida1: =CALCULATE( MAX( Bonos[Bono]),
FILTER( Bonos,
SUM( Ventas[Venta]) >= Bonos[Venta]) )
gracias a esto obtendremos entonces un tabla dinámica que lucirá como la siguiente:
De esto, pasaríamos a hacer multiplicación de dicho campo calculado con el total de ventas para conocer cuánto dinero realmente tenemos que pagar en concepto de bonos a los vendedores.
Dicho esto, creamos otro campo calculado con la siguiente fórmula:
Medida2: =SUM( Ventas[Venta]) * [Medida1]
y obtenemos lo siguiente:
¿Notas algo en particular?
¡La celda del gran total está errada! está multiplicando un 55% por la suma de todas las ventas y no me está brindando el total del dinero que realmente se han adjudicado individualmente los vendedores.
Hagamos un segundo intento con la siguiente fórmula:
Medida3: =SUMX( SUMMARIZE( Ventas,
Ventas[Vendedor],
«Nombre», CALCULATE( SUM( Ventas[Venta]) ) *
CALCULATE( MAX( Bonos[Bono]),
FILTER( Bonos,
SUM( Ventas[Venta]) >= Bonos[Venta]) )
),
[Nombre])
y con ella obtenemos la columna llamada “Bono Correcto” ¡que hace la calculación correcta!
Pero, ¿Por qué funciona esta y no la fórmula anterior? Pues es una incógnita que vamos a descifrar la próxima semana cuando hablemos un poco más sobre subconjuntos de datos calculados en tiempo de consulta, es decir, cómo trabaja la fórmula SUMMARIZE y lo que es el contexto de Filtro.
Puedes comentar y aportar tus ideas sobre porqué funciona la última fórmula y no nuestra primera.
Estimado Miguel.
Felicitaciones por tu blog.
Mi consulta, referente a Power Pivot es como hacer para que me permita “Habilitar actulizacion en segundo plano”.
No estoy pudiendo lograr actualizar una TD alojada en un archivo Excel sin tener que ir a Power Pivot previamente.
Desde ya muchas gracias.
Hola Lucas!
Gracias por tus palabras. Lamentablemente, en Power Pivot para Excel 2010 no es algo integrado por lo que la actualización es algo de 2 pasos (Primero se actualiza Power Pivot y luego tienes que actualizar la TD). Es posible crear un código en VBA que haga dicho trabajo en 1 solo click pero es algo que habría que crear.
Lo más sencillo sería hacer utilizar el Excel 2013 pues ya Power Pivot viene integrado y una actualización en la TD también hará una actualización a tu modelo de datos (ventana de Power Pivot).
Guau!!! Que rapides en tu respuesta Miguel. Gracias Gracias Gracias.
Te cuento que intente actualizarla mediante VBA pero no lo he logrado. Estoy buscando Excel 2013 para ver si asi lo logro, pero teng dudas acerca de como es el funcionamiento, cuando esos archivos se ejecuten en 2010 o anterior.
Que Dios te Bendiga!
Lucas.
Lucas, una vez se haga la actualización de un modelo de datos del Excel 2010 al 2013, no hay forma de revertirlo. Pero lo bueno es que puedes actualizar cualquier modelo de datos creado con Excel 2010 (Power Pivot v2.0 o en base a SQL Server 2012) al Excel 2013.
saludos!
Hola,
Faltan las imágenes de los pasos finales.
Muchas gracias
Gracias!
Lamentablemente tuvimos un percance con los enlaces a las imágenes y las que pudimos obtener no están en la resolución más óptima, pero brindan la idea necesaria.
Nuevamente, gracias por el aviso.
Saludos,
Hola,
Faltan las imágenes de los pasos finales.
Muchas gracias
Hola,
Faltan las imágenes de los pasos finales.
Muchas gracias
Como puedo calcular en Monto Acumulativo en el caso que te muestro?
Dentro de cada Nombre, sumar el monton de cada una de las Letras desde su mayor monton a menor.
Nombre Letra Monto Monto Acumulado
Diego A 756 756
Diego C 675 1.431
Diego B 5 1.436
Juan A 7.657 7.657
Juan E 6.476 14.133
Juan B 5.675 19.808
Juan D 657 20.465
Juan C 34 20.499
Pedro B 3.542.543 3.542.543
Pedro A 145.324 3.687.867
Pedro D 764 3.688.631
Pedro C 654 3.689.285
Saludos.
Hola!
Realmente no estoy seguro de lo que estás buscando, pero te invito a que publiques tu escenario completo junto con archivos de ejemplo en nuestro foro donde los miembros de nuestra comunidad te podrán ayudar. Acá el enlace:
https://foro.poweredsolutions.co/
Saludos!
Orale! en 2021 me ayudó mucho para un reporte de Power BI.
Gran aporte!