Power Pivot: RANKX y TOPN

Acabamos con la serie de Top 3 ventas por cliente con Power Pivot y un poco de DAX.

Obviamente comenzaremos cargando nuestra TblVENTAS al Modelo de datos de Power Pivot, donde crearemos las dos siguientes medidas:

Sum_Ventas:=SUM([Total])
    //////////
    Top3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
                  CALCULATE(SUMX(TblVENTAS; IF (Ordenado<=3; TblVENTAS[Total];0));TOPN(3;TblVENTAS;IF (Ordenado<=3;TblVENTAS[Total];0);DESC))

Comprobamos con la segunda medida como generamos una variable 'Ordenado', equivalente al que teníamos en nuestra hoja de cálculo 'orden', que responde de igual forma, esto es, asignando valores 1, 2, 3, etc. para cada cliente.

Si crearamos una medida solo con la función RANKX:

Orden3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
    Ordenado

comprobaríamos ese aparición ordenada de mayor a menor...

Esta función RANKX es una función iterativa, es decir, recorre cada fila asignando el valor ordinal según la posición o clasificación que le corresponda.
RANKX(table, expression[, value[, order[, ties]]])
Especial interés por los dos últimos argumentos: Orden y Ties
Que toma los siguientes parámetros.
Order: 0/FALSE/DESC – descendente; 1/TRUE/ASC – ascendente.
Ties gestiona la clasificación devuelta en caso de 'empate' o valores repetidos. Skip – el orden de los elementos repetidos se salta...; Dense – todos los elementos empatados se cuentan como uno.

De este cálculo nos interesará recuperar aquellos con ranking inferior o igual a 3, para cada cliente.

Esto lo conseguimos con el retorno de la variable 'Ordenado' en el contexto de CALCULATE y SUMX:

 Top3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
                  CALCULATE(SUMX(TblVENTAS; IF (Ordenado<=3; TblVENTAS[Total];0));TOPN(3;TblVENTAS;IF (Ordenado<=3;TblVENTAS[Total];0);DESC))

donde con CALCULATE podemos aplicar un nivel de filtro de los tres mayores con la función TOPN.
función que nos devuelve, en forma de tabla, las N filas superiores:
TOPN(n_value, table, orderBy_expression, [order[, orderBy_expression, [order]]…])

Así, finalmente podemos recuperar y acumular para cada cliente, exclusivamente aquellos importes del campo 'Total' que tenga nuestra clasificación entre los tres primeros (<= 3)

El resultado, entonces, en nuestra tabla dinámica devuelta a la hoja de cálculo desde el Modelo de datos vemos el resultado...

Aprovechando que tenemos la información cargada en el Modelo de datos haremos uso de las funciones CUBO y un poco de MDX para generar un informe similar.

Empezamos añadiendo en G4 el conjunto de clientes:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblVENTAS].[Cliente].Children";"clientes")
Y a partir de ese conjunto sus miembros en el rango G6:G9:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$G$4;SECUENCIA(RECUENTOCONJUNTOCUBO($G$4)))

Para calcular el total por cliente en H6:H9 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";G6)
obteniendo el acumulado de la medida Sum_Ventas (definida en los pasos anteriores) para cada cliente

Seguidamente crearemos el conjunto de tres elementos de importes más altos para cada cliente, y en I6:I9 insertaremos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"(Filter(Exists([TblVENTAS].[Orden].Children,{[TblVENTAS].[Cliente].["&$G6&"]}),StrToValue([TblVENTAS].[Orden].CurrentMember.Name)<=3),[Measures].[Sum_Ventas])"; "orden<=3")

donde con distintos cruces entre campos y aplicación de filtros obtenemos el importe de 'Sum_Ventas', i.e., el acumulado para el elemento clasificado como 1,2 o 3 del importe 'Total'.

Con el conjunto creado ya podemos recuperar los tres importes más altos; en K6:K9 para el más alto:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;1));0)
en L6:L9 para el segundo más alto:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;2));0)
e igual para M6:M9 y el tercero:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;3));0)

Solo nos queda sumar estas tres cantidades en J6:J9 y habremos acabado...
Más fácil de aplicar en este caso con un poco de DAX ;-)

En cualquier caso, hemos logrado recuperar los tres importes de ventas más altos por cada cliente, de muy distintas maneras.. abriendo el abaníco de opciones.
Espero te resulte de interés.