Après avoir calculé des centiles en MDX (article ici), nous allons faire la même chose dans PowerPivot avec le langage DAX.
Cela marchera également dans un cube tabulaire.

Rappels mathématiques

La formule de base est simple, on récupère la valeur du

p(n+1)/100 –ème élément

p : centile recherché (ici 1 car nous voulons le 1er centile)
n : nombre d’éléments dans l’échantillon

On applique ensuite une interpolation avec l’élément suivant :

1erCentile + (1erCentileSuivant – 1erCentile) * (RangCentileReel – RangCentile)

Les données

Je me base sur AdventureWorks et je récupère les ventes, le catalogue (produit-catégorie) et une dimension temps.

La complexité est que la granularité de la table est au niveau ligne de commande (SalesOrderLineNumber) alors que je souhaite baser mes calculs sur la commande.

Les calculs

• On échantillonne nos données
• On calcule le nombre d’échantillons
• On calcule le rang de chaque échantillon
• On récupère la valeur de l’échantillon au bon rang et au rang suivant
• On interpole

Les formules DAX

L’échantillonnage sera fait dans les différentes formules, en faisant les changements de contexte de filtres (Filter Context).

Le nombre d’échantillons se calcule avec un COUNTROWS.

Le rang de chaque échantillon se calcule avec un RANKX.

Enfin, pour récupérer la valeur de l’échantillon, n’ayant pas la fonction ITEM (il n’y a pas de notion de SET en tabulaire), nous prendrons la dernière valeur d’un TOPN avec un MAXX (ça devrait vous rappeler le BOTTOMCOUNT en MDX).

Attention : Tous ces calculs sont des mesures et non pas des colonnes calculées.

Montant des ventes := SUM(Ventes[Montant])

Rang := RANKX(
  CALCULATETABLE(
    Ventes;
    ALL(Ventes[N° Commande]));
  [Montant des ventes];;1
)

CALCULATETABLE permet de changer le contexte de la fonction RANKX. Le ALL permet de retirer le filtre sur le N° de commande et donc de faire le ranking sur toutes les commandes. Tous les autres filtres qui pourraient être mis sont conservés dans le Filter Context.

Rang1erCentile := CALCULATE(
   ( COUNTROWS(Ventes) – 1 ) / 100 + 1;
   ALL(Ventes[N° Commande])
)

MAXX( Table, Valeur )
TOPN ( N, Table, Expression, Ordre )
(Table étant la table ET son contexte de filtres évidemment)

1erCentile := MAXX(
   TOPN(
      ROUNDDOWN([Rang1erCentile]; 0);
      CALCULATETABLE(Ventes; ALL(Ventes[N° Commande]));  
      [Montant des ventes];
      1);
   [Montant des ventes]
)

1erCentileSuivant := MAXX(
   TOPN(
      ROUNDUP([Rang1erCentile]; 0);
      CALCULATETABLE(Ventes; ALL(Ventes[N° Commande]));
      [Montant des ventes];
      1);
   [Montant des ventes]
)

Les 2 mesures se ressemblent évidemment sauf que l’une prend le ROUNDUP et l’autre le ROUNDDOWN.

Et on finit avec l’interpolation :

Centile:= [1erCentile] + ( [1erCentileSuivant] - [1erCentile] ) * ( [Rang1erCentile] - ROUNDDOWN([Rang1erCentile]; 0) )

Un problème d’agrégation ?

Je l’ai amorcé plus tôt dans l’article mais si vous essayez ces scripts, ils ne fonctionneront pas. Vous aurez des rangs incorrects et donc des centiles incorrects également.

Mais pourquoi ? Le problème vient de la granularité de la table source. Je vous disais que je récupère les lignes de la tables FactInternetSales qui sont à la granularité Ligne de commande donc ma table contient plusieurs lignes par N° de commande.

PowerPivot fait tout ses calculs directement sur la table en mémoire. Il n’y a pas d’agrégation intermédiaire comme dans un cube. Dans la version MDX, la granularité la plus fine du cube est la commande (à cause de ma dimension factuelle Commande, cf. l’article MDX).

J’ai beau changer dans la fonction CALCULATETABLE le contexte de filtre sur le n° de commande, cela n’agrège pas les données et j’ai toujours n-lignes, ce qui décale les rangs.

Les solutions

Tout d’abord, retenez que les scripts ci-dessus fonctionnent si vous avez le bon niveau d’agrégation dans vos données. Pour que cela fonctionne, il suffit de charger un agrégat de la table FactInternetSales (avec un GROUP BY SalesOrderNumber dans la requête).

Cela me permet d’insistez sur un point important dans PowerPivot (mais qui est valable également pour les DataWarehouse). Si vous travaillez sur une granularité N, ne chargez pas les données de la granularité N-1. Même si PowerPivot sait gérer un gros volume de données, cela allègera votre classeur PowerPivot et boostera vos calculs.

Une autre solution (car je connais le précepte “je vais tout mettre, on ne sait jamais…”) est d’utiliser un filtre supplémentaire dans le changement de contexte.

CALCULATETABLE(
   Ventes;
   ALL(Ventes[N° Commande]);
   Ventes[SalesOrderLineNumber]=1
)

Evidemment, je peux utiliser ce filtre car je sais que toutes les commandes ont au moins 1 ligne avec la valeur 1.

Merci à Colin Banfield qui m’a fait gagner un temps précieux et des formules optimisées.