Aujourd’hui, je souhaite vous proposer une méthode afin d’afficher graphiquement (dans un secteur) les n meilleures valeurs et regrouper les autres valeurs comme ceci:
En effet, Excel ne propose pas de solution pour afficher, à travers un tableau croisé dynamique, les autres valeurs, en tout cas de façon dynamique (si le tableau d’origine varie, le graphique sera mis à jour automatiquement).
Je vais donc vous présenter deux solutions, la première d’après un tableau Excel et la seconde d’après des données issues de PowerPivot.
Solution 1: Tableau Excel
Nous allons considérer un tableau très simple, comprenant deux colonnes:
- Dans la colonne A, la liste des produits
- Dans la colonne B, les ventes correspondantes.
Nous pouvons donc voir plusieurs lignes avec le produit « P1 ». L’idée est la suivante: Nous allons créer des nouvelles colonnes afin d’obtenir les x meilleurs produits.
La première colonne à rajouter nous permet, d’obtenir le total des ventes par produit. La formule utilisée sera la suivante (qu’il faudra dérouler jusqu’en bas):
=SOMME.SI(A:A;A4;B:B)
Ensuite nous allons trier la nouvelle colonne par ordre décroissant.
La seconde colonne doit contenir deux formules:
- dans la première ligne: =RANG(C4;C:C)
- dans les autres lignes: =SI(RANG(C5;C:C)-D4>1;D4+1;D4).
Enfin, nous allons créer une dernière colonne avec le nom du produit:
=SI(D4>Limite; »Autres »;A4)
Nous regardons si le rang défini correspond à la limite fixée (dans notre cas 6) et si c’est le cas, alors nous récupérons le nom du produit, sinon nous écrirons « Autres ».
Il ne nous reste plus qu’à construire notre tableau croisé dynamique:
Nous pouvons également très bien appliquer cette méthode aux pourcentage.
Cette solution possède toutefois une limite puisque la première ligne contient une formule différente et donc si le tableau évolue, cela peut poser problème. Si vous avez des solutions plus faciles et plus efficaces, n’hésitez pas à la proposer.
Solution 2: PowerPivot
Dans Powerpivot, nous ne pouvons pas utiliser la même méthode. Toutefois, nous aurons tout de même besoin de créer des colonnes calculées.
La solution que je vous propose est toutefois plus efficace que celle d’Excel puisque dans PowerPivot, nous sommes obligés d’avoir la même formule pour toute la colonne.
Aussi, je vais vous présenter un cas où la relation n’est pas directe. En effet, la gestion des relations entre tables a une importance dans la création des colonnes calculées.
Les données utilisées sont celles proposées par Microsoft et voici un schéma relationnel des tables dans PowerPivot avec les tables que nous utiliserons:
Nous souhaitons avoir le Chiffre d’affaire (FactSales[SalesAmount]) par pays (Geography[RegionCountryName]) puis voir dans un graphique secteur le pourcentage des 6 meilleurs pays ainsi que le pourcentage de l’ensemble des autres pays:
Nous voyons donc ici que « Autres » représente 12% de l’ensemble du chiffre d’affaire de la société.
Afin d’arriver à notre résultat, nous allons créer trois colonnes calculées:
- La première permet de calculer pour chaque ligne le Chiffre d’Affaire. Cela signifie que pour chaque GeographyKey (qui ne correspond pas forcément à un Pays), nous allons calculer le montant des ventes. Nous réaliserons ensuite le calcul du rang d’après cette valeur.
- La seconde permet de réaliser le calcul du rang pour chaque pays;
- La dernière récupérera le nom du pays si celui est dans les 6 meilleurs, sinon nous noterons « Autres » (comme pour le fichier Excel).
Première colonne calculée:
Nous l’appellerons GeoKeyAmount.
La formule sera la suivante:
=CALCULATE(SUM(Stores[StoreAmount]);
FILTER(Stores; Stores[GeographyKey] = Geography[GeographyKey]))
Nous réalisons la somme des ventes selon le champ « GeographyKey ».
Seconde colonne calculée:
Nous l’appellerons RangPays.
La formule sera la suivante:
=RANKX(Geography;
SUMX(FILTER(Geography;
Geography[RegionCountryName]=EARLIER(Geography[RegionCountryName]));
Geography[GeoKeyAmount])
;;0;Dense)
Cette formule est un peu plus complexe.
Tout d’abord la fonction RANKX permet de calculer le rang comme son nom l’indique.
Pour plus d’information sur cette fonction:
D’après notre formule, nous recherchons un classement dans la table Geography.
Outre l’expression que nous allons analyser ci-après, nous utilisons deux options symbolisées par le 0 (pour ordre décroissant) et le mot clé Dense qui signifie que pour la valeur de classement suivante, après une égalité, renverra strictement la valeur de classement suivante.
L’expression que nous utilisons est la suivante:
SUMX(FILTER(Geography;
Geography[RegionCountryName]=EARLIER(Geography[RegionCountryName]));
Geography[GeoKeyAmount])
SUMX retourne la somme de l’évaluation d’une expression pour chaque ligne de la table spécifiée. La table ici correspond à un filtre et l’expression est GeoKeyAmount. Cela signifie que nous ferons la somme correspondant à cette colonne, que nous avons créée précédemment.
Le filtre se fait pour toutes les lignes qui correspondent au Pays. Nous réalisons cela grâce à la fonction EARLIER et ses caractéristiques de récursivité.
Pour plus d’information sur les fonctions SUMX et EARLIER:
Troisième colonne calculée:
Nous allons ici l’appeler Rang6Country.
La formule, beaucoup plus simple sera la suivante:
=IF([RangPays]<=6;[RegionCountryName]; »Autres »)
Le principe est donc le même que pour le fichier Excel. Si nous souhaitons modifier le nombre de meilleures valeurs, soit il faudra modifier la formule, soit créer une autre colonne calculée.
Enfin, il ne nous reste plus qu’à créer notre tableau croisé dynamique et ensuite faire un graphique croisé dynamique comme la copie d’écran suivante:
Pour ceux qui ont d’autres solutions ou des questions, n’hésitez pas à poster sous cet article.