Publicada octubre 23, 2013

Funciones X: RANKX y el HEXAGONAL de la CONCACAF

Power Pivot

Una de las funciones que más he utilizado en mi vida es RANKX(). Pero, qué es lo que hace RANKX?

RANKX() = Función utilizada para hacer ranks o también conocido como clasificaciones

Ahora veamos lo sucedido hace un poco menos de 1 semana en la:

Específicamente el Hexagonal donde mi país, Panamá, lamentablemente perdió al estilo Bayern Múnich 1999 (UCL).

Busquemos los resultados del Hexagonal con Power Query!

Las imágenes dicen mas que mil palabras por lo que les voy a mostrar como encontré los datos del Hexagonal. También me tomo el tiempo de decirles que ha salido una nueva versión de Power Query que pueden descargar desde el siguiente enlace

Luego de tener Power Query instalado, necesitamos ir a la opción de Online Search o búsqueda en línea:

image

luego podemos ver uno de los resultados llamado Standings – 2014 FIFA World Cup qualification – CONCACAF Fourth Round

image

(no pude encontrar una tabla actualizada y la verdad es que me gusta ver a Panamá en el puesto #4 Sonrisa )

le damos clic a ‘Filter & Shape’ para poder revisar la tabla que vamos a importar:

image

debido a que la columna de diferencia de goles original, columna GD, contiene valores de Texto es necesario ya sea transformarlos o, en este caso, opté por crear una nueva columna calculada que hace la resta de ‘GF – GA’. Una vez tengamos esto listo le damos clic a ‘Done’ en la parte inferior derecha de la ventana para entonces importar la tabla a Excel:

image

Luego de que se importe a nuestro Excel necesitamos cargarlo a Power Pivot:

image

para los que deseen el código en M se los dejo abajo:

let
Source = OData.Feed(«http://publicdata.clouddatahub.net/Web/Tables/ddda8716e50542c6b1b26570cab665d2/V1/Data»),
InsertedCustom = Table.AddColumn(Source, «Custom», each [GF]-[GA]),
RenamedColumns = Table.RenameColumns(InsertedCustom,{{«Custom», «Dif de Goles»}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns,{{«Dif de Goles», type number}})
in
ChangedType

Ahora algunas reglas

Según la FIFA, a la hora de hacer los rankings es necesario tomar en cuenta los siguientes:

  1. greatest number of points obtained in all group matches; (mayor cantidad de puntos)
  2. goal difference in all group matches;  (mejor diferencia de goles)
  3. greatest number of goals scored in all group matches. (mayor cantidad de goles)

Al conocer estas reglas simples podemos entonces plantear una fórmula matemática que va a decir algo como lo siguiente en DAX.

La formula para hacer la clasificación del hexagonal en DAX

=RANKX( ALL(‘Tabla’),
CALCULATE( SUM(‘Tabla'[Pts]))/ (10*3)+                                         CALCULATE(SUM( ‘Tabla'[Dif de Goles]))/99999+
CALCULATE(SUM( ‘Tabla'[GF]))/99999  )
)

Nota: he reemplazado el nombre de la tabla original, ‘Standings – 2014 FIFA World Cup qualification – CONCACAF Fourth Round’, por el nombre preferencial de ‘Tabla’ para una lectura más fácil.

Ahora, cómo trabaja RANKX?

Necesitamos, como en todas las funciones X, una tabla y 1 expresión.

La tabla = necesitamos especificar en cual tabla se encuentran los productos, o países en este caso, que vamos a clasificar. Envolvemos dicha tabla con la función ALL() debido a que si no lo hacemos estaríamos utilizando el contexto de filtro que viene de la tabla dinámica el cual en palabras sencillas solamente haría el ranking de Panamá sobre Panamá, USA sobre USA y todos quedarían con el #1. El ALL(), como su palabra lo dice, le dice a la tabla que le brinda TODAS las filas de dicha tabla poder hacer el ranking/clasificación.

La expresión = luego de conocer los países que van a tomarse en cuenta para el ranking, necesitamos un valor numérico el cual nos ayude a poder clasificar cada uno de los países.

Y resultado de ella luce de la siguiente forma:

image

por ultimo expliquemos la fórmula por las secciones de colores:

Se dividen los puntos obtenidos sobre el total de puntos posibles (30 puntos posibles)

Se divide la diferencia de goles sobre un número muy alto. Dicho resultado siempre nos brindará un decimal que puede ser positivo o negativo

Se divide los goles a favor totales sobre un número muy alto. Dicho resultado siempre nos brindará un decimal que siempre será positivo

Dicha fórmula no es la más óptima pero funciona en el 99.9% de los casos. En próximas notas vamos a ver mayor información sobre RANKX y el porque es necesario utilizar dicha formula en vez de utilizar el muy común “Mostrar Valores como”

image

Power Pivot
Subscribe
Notify of
guest
1 Comentar
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
JAP

Buena porte de inicio a dicha función.