Jeanfi a posé une question sur notre forum, à laquelle nous avons décidé de répondre par le biais d’un article de blogue. Jeanfi cherchait donc à créer une mesure qui lui permettrait de comparer une certaine valeur par établissement avec la même valeur cumulée par type d’établissement et par département. Cet article vise à répondre à cette question par le biais du langage DAX avec Power Pivot mais aussi par le biais de Power Query. Plus précisément, nous verrons comment comparer une valeur à une valeur cumulée.
Question originale posée sur notre forum
Voici la question originale qui a été posée sur notre forum. Nous comprenons qu’au final, nous devrons comparer une valeur à une valeur cumulée.
Données de départ
Afin de répondre à cette question, nous avons décidé de simplifier le tout en utilisant nos propres données, que vous pouvez trouver ci-dessous. D’abord, il existe une table de dimensions avec la liste des différents établissements et deux colonnes qui indiquent de quel département et de quel type sont ces établissements. Ensuite, il existe une table de faits avec toutes les valeurs transactionnelles par établissement. Il est donc possible de construire un modèle de données en liant le champ établissement des deux tables.
Approche en DAX
Il y a assurément plusieurs façon d’adresser cette problématique en DAX mais la plus simple est sans doute la suivante. D’abord, ci-dessous, nous avons ajouté 2 colonnes calculées pour récupérer le type et le département de chaque établissement, via une fonction RELATED. Ensuite, nous avons ajouté une colonne calculée pour aller récupérer la valeur cumulée par type et par département. Dans notre exemple, il y a 8 combinaisons possibles (1A, 2A, 3A, 4A, 1B, 2B, 3B, 4B). Pour ce faire, nous avons utilisé la fonction DAX ci-dessous:
Tableau croisé dynamique pour comparer une valeur à une valeur cumulée
En plaçant ces données dans un tableau croisé dynamique, on voit bien que la valeur maximum de chacun de ces calculs (voir colonne calculée en DAX ValeurCum) correspond à la somme de toutes les valeurs de la combinaison type et département sous-jacente.
Résultat final
Au final, on peut donc présenter l’information par établissement et faire fi des types et des départements. Voyez ci-dessous la réconciliation des informations.
Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation des données avec nos formations sur Power Query et Power Pivot dans Excel. |
|---|
Approche avec Power Query pour comparer une valeur à une valeur cumulée
Nous aurions pu obtenir le même résultat en utilisant plutôt Power Query. La plupart des usagers Excel préfèrent effectuer ces manipulations avec Power Query plutôt que d’avoir à utiliser le DAX. Ce n’est pas toujours l’idéal mais c’est souvent plus facile.
Il suffit d’abord de fusionner les données des deux tables afin de récupérer le type et le département de chaque établissement.
Voici le résultat intermédiaire:
On peut ensuite, dans une requête dupliquée, grouper les données par type et par département afin d’en extraire la somme.
On peut ensuite revenir dans notre table initiale et la fusionner avec cette table groupée.
Ceci nous permet de récupérer la valeur cumulée pour chaque combinaison de type et de département.
Le résultat du tableau croisé dynamique est le même.
Nos salutations à Jeanfi. Et en espérant que cet article de blogue a pu répondre convenablement à la question posée.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction au langage DAX, utilisé par Power Pivot et par Power BI Desktop, qui permet de créer des tableaux de bord flexibles et faciles à mettre à jour en plus de créer des visualisations de données évoluées et pertinentes, suivez la formation Introduction au langage DAX (Power BI et Power Pivot).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bonjour et merci pour votre contribution qui a parfaitement répondu à ma problématique? J’ai même approfondi le filtrage avec les utilisateurs et l’année scolaire et ensuite appliqué dans mon TCD la présentation de la valeur en % du total du parent ce qui me permet de comparer l’utilisation des ressources d’un établissement avec l’utilisation de ces même ressources avec les établissements de même type et du même département, et cela par année scolaire et par type d’utilisateur. Merci.