Power BI – Vous rencontrez des problèmes avec l’addition ➕ des heures?

Publié le 28 mai 2020
par Kim Leblanc

Power BI – Vous rencontrez des problèmes avec l’addition ➕ des heures?

Une question a été posée récemment sur le blogue concernant l’addition des heures. La personne voulait calculer le nombre d’heures de communications effectuées par des techniciens. Le résultat devant être sous le format suivant hh:mm:ss ou plus concrètement 54:34:55 pour un résultat de 54 heures, 34 minutes et 55 secondes.

Dans l’exemple ci-dessous, les données sources proviennent d’un fichier Excel et elles sont sous format DATE HEURE. Il faudra faire un peu de bricolage pour utiliser ces données et en arriver à un cumul d’heures…

Pour se faire, on devra passer par Power Query pour calculer le temps en secondes puis utiliser ce résultat dans une mesure en DAX et lui appliquer un format d’affichage approprié.

Voici un aperçu des données qui proviennent d’un fichier Excel.
 

 
Voici le résultat souhaité dans Power BI:
 

 

Première étape, transformer les données de durée en secondes.

Les données sont sous le format hh:mm:ss sous Excel. Par contre, si on regarde de plus près, on remarque que ces heures sont en fait considérées comme des dates. Si on se déplace sur la cellule pour Mathieu au 30 avril, la valeur est 24:08:02, mais si on regarde dans la barre de formule, on peut voir que cette valeur est considérée comme une date soit le 1er janvier 1900 à minuit 8 minutes et 12 secondes ceci explique que lors de l’importation dans Power Query, ces différentes valeurs apparaissent comme des dates.
 

 
Excel stocke les dates sous forme de numéros de série séquentiels afin qu’elles puissent être utilisées dans les calculs. Par défaut, le 1er janvier 1900 correspond au numéro séquentiel 1, ici comme nos données sont des heures, lorsqu’elles sont inférieures à 24 heures, le numéro séquentiel se trouve plutôt à être 0 donc converti comme une journée avant le 1er janvier 1900 donc le 31 décembre 1899. Voici le résultat une fois dans Power Query:
 

 
Pour être en mesure de calculer la durée en secondes, on pourra en premier lieu ajouter une colonne personnalisée contenant la date du 31 décembre 1899 et puis modifier le type pour Date/Heure
 

 

 
Ensuite, il faudra soustraire nos deux dates: [Durée Communication Mois]-[Date Comparaison] et lui attribuer un type de données Durée
 

 
Puis transformer la durée en secondes à l’aide de la fonction Duration.TotalSeconds.
 

 
Une fois le type de données modifié pour nombre entier et les colonnes superflues supprimées, on obtient le résultat suivant:
 

 
Nous sommes donc prêt pour charger le tout dans Power BI et créer notre mesure.
 
 

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.

 

Deuxième étape: Création de la mesure dans Power BI

Une fois les données chargées dans Power BI, on créé une mesure pour calculer le nombre d’heures à partir de la colonne DureeCommunicationSecondes.

Le mesure contient les 4 variables suivantes: Duree, Heures, Minutes et Secondes.
 

 
Voici le comportement de ces différentes variables en utilisant l’exemple de Mathieu pour lequel il y a 290 663 secondes.
 

 
La variable Duree calcule la somme de la colonne DureeCommunicationSecondes
Duree = SUM(Tableau1[DureeCommunicationSecondes])
Duree = 290 663

La variable Heures calcule le nombre d’heures en divisant par 3 600 puisqu’il y a 3 600 secondes dans une heure. On encadre le tout par INT pour arrondir au nombre entier vers le bas. (notez qu’il faudrait plutôt utiliser TRUNC si nous avions des valeurs négatives puisque INT de -3,6 par exemple, donnerait un résultat de -4)

Heures = INT(Duree/3 600)
Heures = INT(290 663/3 600)
Heures = INT(80,739722)
Heures = 80

Pour calculer le nombre de minutes, il faut d’abord obtenir le reste c’est à dire le 0,739722 que nous n’avons pas pris en considération dans le calcul d’heures précédent (pour avoir le nombre entier – INT).
On utilisera la fonction MOD pour aller chercher le reste puis INT pour arrondir le reste au nombre entier le plus bas.
La fonction MOD contient deux paramètres: un Nombre (la durée restante en secondes après la soustraction des heures calculées dans la 1ere variable Heures) et un Diviseur (3 600 secondes)
On divisera ensuite par 60 pour convertir le reste d’heures en minutes.

Minutes = INT(MOD(Duree-(Heures*3600);3600)/60)
Minutes = INT(MOD(290 663-(80*3600);3600)/60)
Minutes = INT(2663/60)
Minutes = INT(44,38)
Minutes = 44

On utilisera ensuite le même principe pour le calcul des secondes

Secondes = MOD(MOD(Duree-(Heures*3600);3600);60)
Secondes = 23

On pourra alors placer nos Heures, Minutes et Secondes au bon endroit avec le calcul suivant:

Heures * 10000 + Minutes * 100 + Secondes
80*10000 + 44 *100 + 23
800000 + 4400 + 23
804423

Mais ce n’est pas tout, si on laisse notre mesure telle quelle, on obtient le résultat suivant:

 

Troisième étape: appliquer un format d’affichage

Il faut ensuite sélectionner notre mesure et à partir du menu Outils de mesure, appliquer le format d’affichage suivant 00:00:00 dans la section Mise en forme.


 
On obtient alors le résultat souhaité.

 


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 :

Carl Landry
Écrit il y a 2 semaines
Formation simple et efficace

Formation simple et efficace donnant une bonne introduction au langage DAX, de même que les outils et ressources afin de l'approfondir davantage. Très intéressant!

Karim Chlebowski
Écrit il y a 2 semaines
Très bonne formatrice qui explique avec passion

Très bonne formatrice qui explique avec passion et avec des termes permettant de bien comprendre des subtilités relatifs au DAX. Je recommande

David Bansard
Écrit il y a 3 semaines
De nombreuses portes se sont ouvertes

Etant, moi-même, dans le métier de la formation, j'avais besoin d'un apprentissage beaucoup plus pertinent et professionnel que se débrouiller tout seul chez soi. De nombreuses portes se sont ouvertes sur la manière de comprendre l'outil Power BI et donc de correctement l'utiliser.

Remi Martinato
Écrit il y a 1 mois
Superbe formation même si le langage Dax est très complexe à comprendre.

Cette formation clôture mon parcours « Excel tableaux de bord » qui a été pour moi extrêmement enrichissant. Cela m a permis de découvrir des fonctions d Excel, de prendre plaisir à les utiliser et de développer ma créativité afin de produire des tableaux de bord encore plus percutants. La formatrice maîtrise sur le bout des ongles les différents outils.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

10 réflexions sur “Power BI – Vous rencontrez des problèmes avec l’addition ➕ des heures?”

  1. Bonjour, si j’ai besoin de calculer moyenne, à la place de la somme, dois-je remplacer “SUM” par “AVERAGE” dans ma mesure initiale?

    j’essaie de calculer la durée moyenne de conversation en minutes et secondes (00:00:00) des employée du call center ou je travaille mais, je n’arrive pas.

    merci de votre aide!!

  2. Bonjour,
    Oui, dans l’exemple donné dans l’article, vous pouvez remplacer la fonction SUM par AVERAGE, mais si vous n’y arrivez pas, vous pourriez déposer votre ficher avec votre question dans la section Forum. nous pourrons vous aider à trouver la solution.

    Au plaisir,

    Kim

  3. Bonjour, j’ai pas pu appliquer le format d’affichage suivant 00:00:00 dans la section Mise en forme.
    Le résultat correcte est 90:00:00 => mais affiché sur Power BI 900000, et la section de la mise en forme est désactivé.
    merci de votre aide!!

    1. Bonjour,
      Quand vous dites que la section mise en forme est désactivée, c’est qu’elle est grisée et que vous ne pouvez pas y entrer de l’information?
      Est-ce que c’est possible que dans votre formule DAX vous passiez le résultat en format texte? En utilisant des & par exemple?

      Il m’est difficile de répondre sans voir votre modèle de données et votre mesure. Si vous le souhaitez, vous pourriez déposer votre ficher avec votre question dans la section Forum. nous pourrons vous aider à trouver la solution.

      https://www.lecfomasque.com/forums

      Kim

  4. Bonjour,merci pour votre réponse!
    En fait moi j’ai tout fait comme indiqué dans la solution que vous avez déposé ci-dessus.
    Je vais déposer mon problème dans la partie Forums avec le fichier .pbix sous le nom Problématique de mise en forme 00:00:00.
    Merci!

  5. Bonjour, merci votre réponse.
    pour bien vous expliquer ,tout d’abord j’ai une colonne où il y a des durées de format Datetime exemple (1899/12/31 00:02:30) et je dois faire la somme de toutes les durées saisies dans cette colonne.
    J’ai suivi les étapes indiqués dans ce blogue ci-dessus:

    Alors les mesures que j’ai fait:
    Date comparaison c’est une colonne personnalisée date comparaison= #date(1899,12,31) pour obtenir seulement le time qui sera stocké dans la colonne nommée Durée (type de la colonne est durée).
    Puis colonne Durée en secondes = Duration.TotalSeconds([Durée]) type de la colonne entier.
    Après j’ai crée les mesures suivantes:
    SommeDuree = SUM(‘mapping'[Duree en secondes]) => type de colonne nombre entier
    Heures = INT ([SommeDuree]/3600)
    Minutes = INT(MOD([SommeDuree]-([Heures]*3600);3600)/60)
    Secondes = MOD(MOD([SommeDuree]-([Heures]*3600);3600);60)
    NbHeures = [Heures]*10000 + [Minutes]*100 + [Secondes] => c’est une mesure pour calculer le nombre des heures total qui est normalement 90:00:00.

    Merci pour votre aide.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top