Contact: 514-605-7112 / info@lecfomasque.com

Somme cumulative conditionnelle dans Power Query

Publié le : 16 septembre 2019

Récemment, un lecteur a demandé sur le forum, s’il était possible de déterminer le chiffre d’affaires cumulatif par produit, par mois, dans Power Query. Après quelques recherches, il a découvert qu’il était possible d’utiliser une fonction de table mais que cette fonction n’était pas très performante, à cause de sa nature itérative. Un autre lecteur, Stéphane Lorin, a alors suggéré une approche alternative, fort intéressante, que j’aimerais aujourd’hui, partager avec vous.

 

Fonction Power Query pour créer une somme conditionnelle

Comme l’a constaté BagTote, il est possible d’utiliser la fonction ci-dessous pour créer une somme conditionnelle dans Power Query. Vous trouverez d’ailleurs cette proposition de fonction sur de nombreux sites web. Bien qu’elle retourne le résultat escompté, sa nature itérative la rend peu performante.

Problème performance

 

Fonction cumulative conditionnelle dans Power Query

L’objectif de notre exercice est d’utiliser les données du tableau en bleu (chiffre d’affaires par produit par mois sur 10 mois) et de calculer le chiffre d’affaires cumulatif par produit par mois (voir colonne K) mais par le biais de Power Query (résultat généré dans le tableau vert et expliqué dans les prochaines lignes).

Somme.si.ens dans Power Query

 

Démarche proposée

1 – Importer le tableau de données mensuelles par produit dans Power Query.

CA par produit

 

2 – Fusionner la requête (table) avec elle-même sur la colonne PRODUIT.

Fusionner une table avec elle-même

 

3 – Extraire la colonne MOIS et la colonne CA de la deuxième table.

Développer table

 

Ici, remarquez que chaque produit par mois est ainsi développé 10 fois (voyez notamment le CA de 42 qui revient 10 fois pour le produit 1, pour le mois de janvier).

Tables fusionnées

 

5 – Effectuer un filtre sur la deuxième colonne de MOIS pour ne retenir que les MOIS inférieurs au mois en cours (remarquez ici le 42 qui n’apparaît qu’une seule fois, le 12 qui apparaît 2 fois, le 73 qui apparaît 3 fois, etc).

Filtre mois

 

6 – Regrouper ensuite les données par produit, par mois et par CA, pour revenir à la table initiale, et effectuer la somme des CA.

Regrouper par

 

Cette façon de faire permet de calculer aisément le total cumulatif par produit, par mois, et est plus performante car elle n’invoque pas de fonction pour chacune des lignes de la table.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. 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.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire