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.
Question originale posée sur notre forum
Voici la question originale qui a été posée sur notre forum.
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
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.
Approche avec Power Query
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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre un vaste choix de formations Excel, notamment en tableaux de bord et en modélisation financière, ainsi qu’une série de formations en lien avec Power BI. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel. De plus, nos formations peuvent mener à l’obtention d’une accréditation.
Découvrez quelles formations vous conviennent
Pour info: 514-605-7112 ou info@lecfomasque.com