Archivo el 25/08/2020

Patrones DAX. Rankings

Hace unas semanas Alberto Ferrari y Marco Russo lanzaron la segunda edición de su libro DAX Patterns cuya primera versión databa del año 2014 y el DAX que utilizaban había quedado ya algo desfasado. En este libro, establecen una serie de patrones en DAX altamente optimizados para resolver muchísimos problemas de medidas complejas que se suelen dar cuando elaboramos nuestros reportes en Power BI. Es un libro 100 % recomendable que debemos tener como libro de cabecera de DAX junto a su clásico “The Definitive Guide to DAX”

Uno de los patrones que más me gusta es el tratamiento de los rankings, muy necesario en nuestro día a día en la creación de reportes avanzados.

Me voy a permitir el lujo de explicaros en español los cálculos necesarios para la creación de rankings siguiendo los patrones de Ferrari y Russo.

Podéis,  por supuesto consultar la fuente original en inglés en el siguiente enlace:

https://www.daxpatterns.com/ranking/#

En los patrones de SQLBI menciona dos tipos diferentes de ranking, los estáticos y los dinámicos basados todos ellos en la función RANKX.

Ranking estático

El ránking estático se crea a partir de columnas calculadas y tiene la particularidad de que sea cual sea el contexto de filtro existente en el reporte el ranking no va a variar. Si por ejemplo hacemos un ranking de productos basado en el total de ventas de cada producto utilizando para ello la medida [Total Ventas] sin ningún tipo de filtro, nos devolverá el ranking de todos los productos que tengan ventas en la base de datos independientemente de cuando o donde se hayan producido las ventas

Personalmente no es una técnica que me agrade en exceso ya que va a tener poco sentido en un reporte de ventas establecer rankings estáticos. No obstante, vamos a conocer su funcionamiento.

La fórmula para realizar un ranking estático de productos es muy sencilla y la realizaremos en una columna calculada:

RANKX(  Productos , [TotalVentas])

La fórmula que Ferrari y Russo proponen en sus patrones es realmente RANKX( ALL (Productos), [Total Ventas]) pero ellos mismos apostillan que el ALL en este caso es innecesario y simplemente lo utilizan por una mejor legibilidad de la métrica.

Como podemos observar en el ejemplo, las ventas totales aparecen correctamente rankeadas

Sin embargo, si establecemos un filtro como por ejemplo el ejercicio 2019, el ranking, que es estático, no va a obedecer a los datos que mostramos de ventas:

Hay que tener muy claro que es un ranking estático. Podríamos sin embargo hacer un ranking de las ventas de un ejercicio determinado como ranking estático sin ningún problema:

RANKX(Productos,
CALCULATE([Ventas],FILTER(Fechas,Fechas[Ejercicio]=2019)))

En este caso, como podemos observar al tener el filtro del 2019 nos coincide con el ranking que hemos llamado Ranking 2019.

Otro aspecto que podemos controlar, aunque siga siendo una clasificación estática, es dentro de esa dimensión producto establecer rankings para alguna subdivisión como podría ser la subcategoría o la categoría de producto. Para ello entraría en juego la función ALLEXCEPT de manera que si quisiéramos establecer un ránking estático en cada familia de producto la métrica quedaría de la siguiente forma:

Ranking ventas en subfamilia =
RANKX( 
ALLEXCEPT(
  Productos, Productos[Subfamilia]
               ),
               [Ventas]
               )

Y así quedaría nuestra visualización:

Ranking dinámico:

Lo verdaderamente potente para nuestros reportes es precisamente la posibilidad de realizar los rankings de manera dinámica. En este caso ya no los haríamos sobre columnas calculadas sino utilizando medidas. Vamos a explicar cómo:

RANKX (
            ALLSELECTED ( [Producto] ),
            [Ventas]
        )

Esta fórmula en una medida funcionaría perfectamente. De hecho si la vemos en la tabla funciona perfectamente:

Y si filtramos por el 2019 vemos como se adapta a los resultados del Ranking 2019 que teníamos de manera estática:

Pero hay algunos aspectos que debemos mejorar en la fórmula:

1º Evitar que ranquee aquellos productos que no hayan tenido venta:

2º Evitar que se produzcan errores en el caso de que no estemos en el contexto de producto adecuado:

Esto segundo lo haremos envolviendo la fórmula en un IF (ISINSCOPE([Producto].[Producto]) sólo cuando estemos en el contexto de filtro de producto nos hará el cálculo. Y para lo primero, determinaremos si existen ventas para ese producto y solo si existen haremos el cálculo. De esta manera quedaría así la fórmula:

Con esta fórmula definitiva, podemos observar que no hay Ranking si no hay venta y que no hay ranking si no estamos a nivel de articulo:

IF (
    ISINSCOPE ( 'Productos'[Producto] ),
    VAR __VentasProducto = [Ventas]
    VAR __Ranking =
        RANKX (
            ALLSELECTED ( 'Productos' ),
            [Ventas]
        )
    VAR Resultado =
        IF (
            NOT ISBLANK ( __VentasProducto ),
            __Ranking
        )
    RETURN
        Resultado
)

También nos valdría de esta manera:

Ranking Dinámico = 
var __Ventas = [Ventas]
return
IF (
    ISINSCOPE ( 'Productos'[Producto] ) && NOT ISBLANK(__Ventas),
        RANKX (
            ALLSELECTED ( 'Productos' ),
            [Ventas]
        )
)

Me van a perdonar los amigos Ferrari y Russo pero creo que es más sencilla de leer y de explicar la  mía y a nivel de rendimiento no se aprecia diferencia:

Podemos necesitar, como hacíamos en el ranking estático, establecer el ranking para cada una de las subcategorías. Con el cálculo que hemos creado, da igual que lo analicemos desde el punto de vista de la subcategoría, ya que muestra la posición en el ranking total si están todos los productos seleccionados:

Si filtrásemos una única subfamilia, el dinamismo del cálculo haría que para esa subfamilia presentase su ranking. Pero si tenemos 2 subfamilias filtradas, por ejemplo, el ranking sería conjunto de todos los productos de ambas:

Es por ello que podemos hacer, igual que hicimos el cálculo estático, el ranking de los productos en la subcategoría de la siguiente forma:

En este caso os voy a poner sólo mi fórmula con un solo IF:

Ranking Producto en subfamilia 
var __Ventas = [Ventas]
var __ProductosenSubfamilia = CALCULATETABLE(Productos,
                                REMOVEFILTERS(Productos[Producto]),
                                ALLSELECTED(Productos),
                                VALUES(Productos[SubFamilia]))
                                
return
IF (
    ISINSCOPE ( 'Productos'[Producto] ) && NOT ISBLANK(__Ventas),
        RANKX (__ProductosenSubfamilia,
        [Ventas])
        )

Estamos creando una tabla que nos devuelve el contexto de cada subfamilia, como hacíamos con el ALLEXCEPT a nivel de fila en la columna calculada y sobre esa tabla es sobre la que creamos el ranking.

Si traemos este cálculo a la visual anterior observamos como para cada una de las subfamilias seleccionadas realiza su correspondiente ranking:

TopN por subfamilia.

Por último, si en la tabla anterior filtrásemos El Ranking Producto en subfamilia por los 3 primeros, por ejemplo, obtendríamos el siguiente resultado para todas las subfamilias:

Esto lo podemos también construir con una medida aprovechando la función TOPN, e incluso podemos dinamizar el número de elementos que queremos visualizar, sobre todo porque no podríamos saber el total de ventas de cada TopN sin mostrar los productos. Sin embargo con una medida si, de la siguiente manera:

Primero vamos a crear un parámetro What If:

Y luego vamos a crear el TopN Ventas por subfamilia = 

TopN Ventas por subfamilia = 
var __topN= SELECTEDVALUE('TopN'[TopN])
VAR __TopNProductos =
    GENERATE (
        ALLSELECTED ( 'Productos'[SubFamilia]),      -- Para cada subfamilia
        TOPN (                                  -- Generamos el top N
            __topN,                                       
       ALLSELECTED ( 'Productos'[Producto] ),    -- Basado en el nombre seleccionado
            [Ventas]                              -- por ventas
        )
    )
VAR Resultado =
    CALCULATE (
        [Ventas],                        -- Computamos las ventas
        KEEPFILTERS ( __TopNProductos )  -- Usando como filtro la tabla de TopNProductos
    )                                        
RETURN
    Result
ado

De esta manera con el parámetro TopN controlamos el número de productos que queremos ver por cada subfamilia: