Jauges avec slicer

Power Pivot + Dax + Excel - Facile

Tags : CALCULATE ; ALL ; Segments ; VALEURCUBE ; Excel ; Jauge

Temps de préparation : 30 minutes

Définition : Une Jauge est un moyen décisionnel de représenter un KPI ou indicateur clé. Elle permet de représenter l’atteinte d’un objectif par rapport à des seuils prédéfinis sous la forme d’un compteur dont les seuils sont disposés de manière linéaire sur l’objet et l’atteinte de l’objectif est représenté par un niveau ou une aiguille.

Préparation de la recette :
Le modèle que j’utilise est un modèle de données basée sur des données provenant des nations unies concernant la vente de fruits dans le monde pendant les 50 dernières années. Le modèle possède une table de fait de production, une dimension fruit, une dimension géographique et une dimension temps. (Note : je détaillerai la constitution de ce modèle dans un prochain billet)
Dans cet exemple, il faut s’intéresser à la fonction Calculate, cette fonction permet de réaliser un calcul dans un contexte de données différent du contexte initial. La fonction attends deux paramètres, le premier est la table sur laquelle le calcul s’opère, les paramètres suivants sont des filtres sur le contexte.
De plus, la fonction ALL permet de fournir une table non filtrée.
Enfin, Power Pivot permet d’utiliser des mesures dans le calcul d’autres mesures, ce qui évites des formules longues et complexes.

L’idée est de représenter une jauge par rapport à l’atteinte d’un objectif. Pour ce faire, on va utiliser une première technique qui consiste à simuler une jauge à l’aide de camembert et d’anneau.
On utilise une seconde technique relative à Power Pivot pour animer la jauge à l’aide de mesures calculées ce qui va nous permettre de prendre parti des segments pour filtrer la jauge.
Enfin on utilise la technique de récupération des données d’un cube dans Excel afin de constituer le tableau des valeurs nécessaires pour les graphiques.

Pour constituer la jauge, je vais constituer 2 tables de données :

- La première concerne les seuils (vert jusque 75% et rouge pour le reste)
- La seconde concerne l’aiguille.

Pour alimenter la table concernant l’aiguille, il me faut disposer des informations suivantes :

- Le demi-anneau du bas 
- La position de l’aiguille 
- L’épaisseur de l’aiguille
- Le reste du cadran

Comment les constituer ?
Dans Power Pivot au niveau de la table de fait Production je crée les mesures suivantes :

Nom	Formule Dax	Explications
Base	Base:=180	Il s’agit de la base de l’anneau
TotalMesure	totalMesure:=CALCULATE(Sum(Production[Quantité]);ALL(Production))	En utilisant calculate cette mesure me donne le total sans prendre en compte les filtres appliqués sur la page où les segments.
Pct	Pct:=Sum(Production[Quantité])/[totalMesure]*100	Cette mesure calcule l’indicateur en pourcentage
Aiguille_Pos	Aiguille_Pos:=(180/100)*[Pct]-1	Cette mesure donne la position de l’aiguille
Aiguille_Width	Aiguille_Width:=5	Cette mesure donne l’épaisseur de l’aiguille
Jauge	Jauge:=360-([Aiguille_Pos]+[Aiguille_Width]+180)	Cette mesure donne l’espace restant de la jauge

Ce qui nous donne :

 

On se place dans Excel et on fait la table des seuils :

Ensuite, on sélectionne la colonne des degrés et on insère un graphique :
Notez que le 180 est une valeur que l’on impose afin de couper l’anneau en deux et qui ne suit pas la règle de la formule. Ensuite on sélectionne la colonne degré et dans le menu insertion, on ajoute un anneau.

Ce qui nous donne l’anneau suivant :

On supprime la légende, et on sélectionne le demi-anneau, clic droit, et on choisit mettre en forme une série de données.
On va sur l’onglet Option des séries dans la fenêtre de modification des séries de données et on change l’angle du secteur 1 à 90°, dans remplissage, on choisit aucun remplissage pour le secteur 1.
Je supprime le titre et la légende.
J'insère un segment et une chronologie :

Pour le Segment : dans le menu insertion, on choisit nouveau segment. On choisit le modèle de données

On se connecte à notre modèle de données et on sélectionne la dimension fruits.

Dans Outils segment à Options à paramètres des segments

Pour la chronologie, on procède de la même manière que pour le segment à ceci près que l’on ne changera pas le nom et que le nom date sera imposé avec une incrémentation pour toutes les chronologies.
On constitue le tableau de l’aiguille dans la feuille Excel:

On copie le graphique précédent, on le colle sur la même feuille et on le déplace pour qu’il soit exactement au-dessus du graphique initial.
Je clic droit sur le graphique et dans le menu contextuel je choisis sélectionner les données.
Je supprime la série d’entrées de légende et j’en ajoute une nouvelle qui correspond à la colonne degré du tableau "Aiguille".

J’obtiens un camembert avec une tranche plus fine correspondant à l’aiguille.
Je clique sur les autres catégories et je choisis pour chacune d’entre elles aucun remplissage, je modifie également le remplissage de la tranche de l’aiguille pour la mettre en noir.
Je clic droit sur le fond du graphique et je choisis aucun remplissage ce qui me permet de voir le graphique d’en dessous.
J'obtiens le graphique suivant :

On ajoute le texte de l’atteinte d’objectif
Dans une cellule on met la formule suivante : ="Atteint : " & ARRONDI(VALEURCUBE("ThisWorkbookDataModel";"[Measures].[Aiguille_Pos]";Segment_Fruits1;Chronologie_Date1);0) & " %"
- J'ajoute sur la feuille une zone de texte en allant sur insertion à text à zone de texte
- Dans la barre de formule de la zone de texte on écrit la formule = $D$2 ou D2 est dans mon cas la cellule contenant le titre.
- Je place la zone de texte dans le graphique

Le résultat est le suivant :

Pour aller plus loin :
De la même manière, on peut utiliser les mêmes techniques pour faire une jauge de ce type-là

Ou encore utiliser une image de fond plutôt qu’un autre graphique.