Power BI avec le suivi budgétaire HUT

Partie 1

Ce que fait Power BI

1. SourcesFichiers Excel ou CSV, bases de données. Ici : le grand livre et les référentiels.
2. Power QueryNettoie et prépare : colonnes utiles, types, comptes 6 seulement.
3. ModèleRange les données en tables reliées : écritures, budget, sous-conventions…
4. Mesures DAXLes calculs : réalisé à date, projection, statut.
5. RapportLes pages, les visuels et les segments que l'on regarde.
6. ServiceLe site de Microsoft où l'on publie pour partager. Facultatif.

Dans Power BI Desktop, les étapes 2 à 5 ont chacune leur écran. Le cours les suit dans cet ordre.

Si vous connaissez Excel

Partie 2

L'écran de Power BI Desktop

À l'ouverture de Suivi_budgetaire_HUT.pbip, Power BI affiche la vue Rapport, sur la première page.

1. Les onglets du ruban. Accueil regroupe l'essentiel. D'autres onglets apparaissent selon ce que vous sélectionnez : Mise en forme, Outils de mesure…
2. Actualiser relit les fichiers sources et recalcule tout. À faire après chaque nouvel export du grand livre.
3. Les vues, de haut en bas : Rapport (les pages), Table (les données), Modèle (les relations), puis Requête DAX et TMDL, pour plus tard.
4. Le canevas : la page du rapport en cours de modification.
5. Les segments : des filtres que le lecteur manipule (Département, Convention, Nature, Mois d'arrêté).
6. Un visuel, ici un tableau. Chaque cadre de la page est un visuel : carte, tableau, graphique, segment.
7. Le volet Filtres, replié. Il porte les filtres réglés par l'auteur du rapport (partie 8).
8. Le volet Visualisations : choisir le type de visuel et y placer des champs.
9. Le volet Données : toutes les tables du modèle et leurs champs.
10. Les onglets des pages : 10 pages dans la maquette.

Partie 3 · Étape 2 de la chaîne

Préparer les données avec Power Query

Accueil > Transformer les données ouvre l'éditeur Power Query, dans une fenêtre à part. Chaque requête lit une source et lui applique une suite d'étapes. Voici la requête GrandLivre, qui lit l'export du grand livre.

1. La liste des requêtes. Une requête produit une table. Celles en italique ne sont pas chargées dans le modèle : ce sont des paramètres ou des étapes intermédiaires.
2. Les paramètres : DossierDonnees (où sont les fichiers), Exercice (l'année suivie), SeuilAutresSankey. Pour changer d'année ou de dossier, on change un paramètre, pas les requêtes.
3. La barre de formule : le code M de l'étape choisie. Inutile de l'écrire : chaque clic du ruban ajoute une étape et son code.
4. L'aperçu : les premières lignes de la table à cette étape.
5. Le type de chaque colonne : ABC pour du texte, 1.2 pour un nombre, un calendrier pour une date. Un mauvais type est la première cause d'erreur.
6. Les étapes appliquées, dans l'ordre. Cliquer sur une étape montre la table à ce moment-là ; la croix la supprime.
7. Fermer et appliquer charge le résultat dans le modèle.
8. Gérer les paramètres : modifier DossierDonnees, Exercice…

Partie 4 · Étape 3 de la chaîne

Relier les tables : le modèle

Après Power Query, les tables arrivent dans le modèle. La vue Modèle montre comment elles sont reliées. C'est ce qui permet à un seul segment de filtrer à la fois les dépenses et le budget.

1. Écritures est une table de faits : une ligne par écriture comptable, avec un montant. Elle grossit chaque mois. Budget, ETP, Flux Sankey et Arbre sont aussi des tables de faits.
2. Sous-conventions est une table de dimension : une ligne par code analytique, avec sa convention, son département, son financeur. Calendrier et Natures aussi.
3. La relation sélectionnée relie Écritures[Code] à Sous-conventions[Code]. Le « 1 » est du côté où chaque code n'apparaît qu'une fois, le « * » du côté où il revient souvent. La flèche indique le sens du filtre : du 1 vers le *.
4. Ses propriétés : cardinalité Plusieurs à un, filtre croisé Unique, relation active.
5. Des tables sans relation (Arrêté, Grandeurs) : elles alimentent des segments que les mesures lisent elles-mêmes, comme le mois d'arrêté ou le choix entre euros et ETP.
6. Mesures : une table vide qui sert de rangement aux 59 mesures du modèle.
7. Gérer les relations : la liste de toutes les relations, pour en créer ou en modifier.
8. La vue Modèle.

Partie 5

Regarder les données : la vue Table

La vue Table montre le contenu d'une table du modèle, après Power Query. On y vérifie les données ; on ne peut pas les modifier.

1. La vue Table.
2. Les en-têtes de colonnes. La flèche trie ou filtre l'affichage, pour regarder ; le rapport n'en est pas changé.
3. Les lignes : les 440 écritures de charges de l'exercice.
4. La colonne sélectionnée : Montant. Elle est déjà mise au réel pour la convention 06, d'où la colonne voisine « Montant grand livre ».
5. Outils de colonne : type de données, format (€), et Résumé : Somme, ce que Power BI fait quand on glisse cette colonne dans un visuel.
6. Le champ dans le volet Données. Σ signale une colonne numérique.
7. La barre d'état : nombre de lignes et de valeurs distinctes. C'est le premier contrôle à faire après une actualisation.

Partie 6 · Étape 4 de la chaîne

Calculer avec des mesures DAX

1. La barre de formule : le code de la mesure. ✓ valide, ✕ annule.
2. La mesure dans le volet Données (icône de calculatrice), rangée dans le dossier « 2. À l'arrêté ».
3. Son nom, sa table et son dossier d'affichage.
4. Son format : #,##0 €, des euros sans décimale.
5. Nouvelle mesure crée une mesure vide dans la table sélectionnée.
6. La carte qui affiche cette mesure sur la page.

La formule, ligne par ligne

Réalisé à l'arrêté =
VAR A = [Mois d'arrêté]                -- le mois choisi, sinon le dernier mois du grand livre
RETURN
    CALCULATE (                           -- calcule…
        [Réalisé],                        -- … la mesure Réalisé (somme des montants comptés)
        'Calendrier'[N° mois] <= A,       -- … de janvier au mois d'arrêté
        REMOVEFILTERS ( 'Calendrier' )    -- … même si un mois est choisi ailleurs
    )

Une formule, trois résultats

On écrit une seule formule. Chaque case apporte ses propres filtres, et Power BI fait le reste.

Les fonctions DAX à connaître d'abord

SUM ( colonne )

Additionne une colonne. Budget = SUM ( 'Budget'[Budget] ).

CALCULATE ( mesure, filtres… )

Calcule une mesure en changeant les filtres. C'est la fonction centrale du DAX.

DIVIDE ( a, b )

Division qui renvoie une case vide au lieu d'une erreur quand b vaut 0. Utilisée dans « Écart à l'arrêté % ».

VAR … RETURN

Donne un nom à un résultat intermédiaire. La formule devient lisible et vérifiable.

SELECTEDVALUE ( colonne, défaut )

La valeur choisie dans un segment, s'il n'y en a qu'une. Utilisée dans « Mois d'arrêté ».

IF, SWITCH

Choisir selon une condition. « Statut » renvoie 🟢, 🟠, 🔴 ou ⚪ selon l'écart projeté et la tolérance de la convention.

REMOVEFILTERS, ALL

Ignorer un filtre, comme le mois dans la formule ci-dessus.

Partie 7 · Étape 5 de la chaîne

Construire un visuel

1. Le visuel sélectionné : poignées aux coins ; en haut à droite, le filtre, le mode Focus et « … » (trier, exporter les données).
2. La galerie. Le type en surbrillance est « Graphique en courbes et histogramme empilé ». Une autre icône change le type en gardant les champs.
3. Axe X : les mois (Calendrier[Mois court]).
4. Axe Y de colonne : deux mesures empilées, le personnel et le fonctionnement.
5. Axe Y de ligne : le budget du mois, dessiné en trait.
6. Les champs utilisés sont cochés dans le volet Données.
7. Le pinceau : mettre en forme ce visuel (couleurs, titres, étiquettes, axes).
8. Une matrice, le tableau croisé de Power BI : sous-conventions en lignes, mois en colonnes.

Partie 8

Filtrer : segments, volet Filtres, filtre croisé

Sur la page 1, on choisit Nature = Fonctionnement. Toute la page se recalcule.

1. Le segment Nature, ouvert, avec Fonctionnement coché. Ctrl + clic pour en cocher plusieurs.
2. Les cartes sont recalculées pour le seul fonctionnement : budget 346 000 €, réalisé 309 719 €.
3. « 01 · Foyer de nuit » passe en 🔴 : +13,0 % sur le fonctionnement (hausse des prix de l'alimentation), alors que la sous-convention entière reste conforme.
4. Le volet Filtres, ouvert : filtres sur ce visuel, sur cette page, sur toutes les pages.
5. La colonne ETP se vide. C'est voulu : la mesure ETP renvoie une case vide quand une nature est choisie, car les ETP ne se partagent pas entre personnel et fonctionnement.

Trois façons de filtrer

Partie 9

Mise en forme : la couleur selon la valeur

La carte de chaleur de la page 2 est une matrice dont le fond de chaque case dépend d'une mesure. C'est la mise en forme conditionnelle.

1. Volet Mise en forme > Valeurs de cellule > Couleur d'arrière-plan, puis le bouton fx, qui ouvre la fenêtre.
2. Le pinceau, « Mettre en forme le visuel ».
3. Style de format : Dégradé. Les autres choix : Règles (des seuils) ou Valeur du champ (une couleur calculée).
4. La couleur suit la mesure Couleur écart cumulé, bornée à ±30 % pour que quelques extrêmes n'écrasent pas le dégradé.
5. Trois couleurs : bleu pour la sous-consommation, blanc à 0, terre cuite pour le dépassement.

Exemple de lecture : « 02 · Formations des bénéficiaires » est bleue de mai à août (−27,2 % en mai) puis revient à +1,8 % en septembre. La dépense est arrivée plus tard que prévu au budget ; il n'y a pas de sous-consommation sur l'année.

Partie 10

Explorer : l'arbre de décomposition

L'arbre de décomposition découpe un total niveau par niveau, au clic. La maquette en a trois pages (par convention, département par conventions, département toutes conventions), plus une page « tout déplié » sous forme de tableau.

1. La racine : la valeur analysée, 2 021,5 k€ de dépenses de janvier à septembre.
2. Un niveau : le champ Département. La croix le retire.
3. Le nœud choisi : Hébergement, 589,9 k€. Cliquer un nœud ouvre le niveau suivant.
4. Le + : ajouter un niveau, ou laisser Power BI choisir le découpage le plus marquant (valeur haute ou basse).
5. Le segment Grandeur : dépenses en k€ ou ETP. La même page sert aux deux.
6. Les notes « Pour lire l'arbre » : une table dont le texte est calculé par une mesure, donc toujours à jour.

Au dernier niveau, le personnel du Foyer de nuit est réparti entre les métiers avec la clé de sa convention (onglet Clé métiers) : veilleur de nuit 137,3 k€, travailleur social 115,6 k€, agent d'entretien 50,6 k€. Au survol d'un nœud, une info-bulle donne le montant exact et les parts.

Partie 11

Ajouter un visuel : le Sankey

Le Sankey n'est pas livré avec Power BI. On l'ajoute depuis AppSource, la bibliothèque de visuels de Microsoft : volet Visualisations > « … » > Obtenir plus de visuels > « Sankey Chart ». La page Sankey de la maquette explique la manipulation, qui prend deux minutes.

1. Le Sankey ajouté depuis AppSource : Source et Cible en champs, la mesure Valeur Sankey en poids.
2. Un nœud partagé : « Personnel » reçoit les flux des trois sous-conventions. Deux nœuds de même nom sont toujours fusionnés.
3. Le tableau des flux : la table Flux Sankey (Étape, Source, Cible, Valeur). Le Sankey est une autre façon de dessiner ce tableau.
4. Le segment Étapes affichées : l'étape 1 (département → convention) est décochée.
5. Le segment Département = Hébergement.
6. L'épaisseur d'un flux est proportionnelle au montant : 182 860 € vers « Veilleur de nuit ».

Certaines organisations bloquent les visuels d'AppSource. C'est pour cela que la maquette garde le tableau des flux et les pages d'arbre, qui donnent les mêmes chiffres.

Partie 12 · Étape 6 de la chaîne

Partager : le service Power BI, en bref

• Publier (Accueil > Publier) envoie le rapport et ses données dans un espace de travail du service Power BI, sur le cloud de Microsoft. Pour les vraies données de HUT, cette décision revient à HUT.
• Dans le service, on lit le rapport dans le navigateur, on le partage avec des comptes nommés, ou on le regroupe dans une application pour les lecteurs.
• L'actualisation planifiée relit les sources chaque jour. Pour des fichiers posés sur un poste ou un serveur interne, il faut une passerelle de données installée chez HUT.
• La sécurité au niveau des lignes limite chaque lecteur à ses données. Modélisation > Gérer les rôles, avec un filtre DAX comme 'Sous-conventions'[Département] = "Santé" : chaque chef de département ne voit que le sien.
• Partager demande en général une licence Power BI Pro pour l'auteur et pour les lecteurs, ou une capacité Premium ou Fabric. À voir avec l'informatique de HUT.

Partie 13

Exercices sur la maquette

À faire sur votre PC, maquette ouverte et actualisée : suivez « Ouvrir la maquette » dans le LISEZMOI.md du dossier maquette_power_bi. Chaque exercice a sa réponse attendue. Cochez-le quand il est fait ; la coche reste dans ce navigateur.

0 exercice fait sur 7

Part du personnel =
DIVIDE (
    CALCULATE ( [Réalisé à l'arrêté], 'Natures'[Nature] = "Personnel" ),
    [Réalisé à l'arrêté]
)

Partie 14

Les pièges du débutant

• Corriger une donnée dans Power BI. Ce n'est pas possible, et c'est voulu : corrigez la source, ou ajoutez une étape Power Query.
• Glisser une colonne numérique dans un visuel. Power BI crée une somme automatique, sans format ni règle. Écrivez plutôt une mesure explicite, que vous pourrez réutiliser partout.
• Oublier une relation, ou en ajouter une de trop. Un segment sans effet signale souvent une relation manquante. Gardez le filtre croisé « Unique » : « Les deux » crée des résultats difficiles à expliquer.
• Les mois dans le désordre. Triez la colonne du mois par son numéro (Trier par colonne).
• Confondre Enregistrer et Actualiser. Enregistrer garde le rapport ; Actualiser relit les sources. Après un nouvel export, il faut les deux.
• Un chemin de fichier en dur. Si les données changent de dossier, modifiez le paramètre DossierDonnees, avec le \ final.
• Une case vide n'est pas un zéro. Une mesure peut renvoyer « vide » quand rien ne correspond aux filtres (exercice 5). Une ligne dont toutes les mesures sont vides disparaît du tableau.

Partie 15

Lexique

Rapport

L'ensemble des pages que l'on regarde.

Visuel

Un cadre de la page : graphique, tableau, carte, segment.

Segment

Un visuel qui sert de filtre au lecteur.

Modèle sémantique

Les tables, leurs relations et les mesures. Le rapport s'appuie dessus.

Table de faits

Une table longue d'événements chiffrés : écritures, budget mensuel.

Table de dimension

Une table courte de description : sous-conventions, calendrier.

Relation, cardinalité

Le lien entre deux tables par une colonne commune ; « plusieurs à un » dans la plupart des cas.

Mesure

Une formule DAX calculée à l'affichage, selon les filtres de chaque case.

Colonne calculée

Une colonne ajoutée par une formule, calculée à l'actualisation.

Contexte de filtre

L'ensemble des filtres qui s'appliquent à une case : segments, ligne du tableau, volet Filtres.

Power Query, langage M

L'outil de préparation des données et le langage de ses étapes.

Étape appliquée

Une transformation enregistrée dans une requête, rejouée à chaque actualisation.

Paramètre

Une valeur nommée utilisée par les requêtes : dossier, exercice, seuil.

.pbix, .pbip

Le rapport en un seul fichier, ou en dossier de projet lisible ligne à ligne.

Espace de travail

Un dossier partagé du service Power BI.

Sécurité au niveau des lignes

Des rôles qui limitent les lignes visibles par chaque lecteur.

Pour aller plus loin

• Bien démarrer avec Power BI Desktop, sur Microsoft Learn.
• Référence du langage DAX, sur Microsoft Learn.
• DAX Guide : chaque fonction avec ses exemples (en anglais).