Création de tableau de bord, BI et entrepôt de données

lundi 6 janvier 2014

PowerPivot utilisation du DAX - Calcul avec deux niveaux d'agrégation - DAX avec et PowerPivot MS-Excel

Créer des rapports avec deux niveaux d’agrégation n'est pas toujours évident. Voici un exemple de rapport graphique conçu à l'aide de "PowerPivot pour Excel 2010vous démontrant cette possibilité.

Les données ont été extraites d'un progiciel de gestion intégré (PGI ou ERP en anglais) dans le domaine financier combiné avec les données budgétaires. La table de faits contients plus de 8 millions d'enregistrements et peux être analysée selon 12 axes différents aux choix de l'utilisateur. MS-Excel doit transporter les données sur le poste de l'utilisateur et la puissance de la base de données n'est pas mise à contribution pour créer ces requêtes qui donnent des résultats moins rapidement en général. Les données doivent être rechargées sur le poste de travail à la création du rapport et à chaque actualisations des données.

Les mesures: Vous pouvez observer dans le rapport graphique ci-dessous, les deux mesures agencées en deux colonnes de couleurs distinctes. La colonne bleue étant le montant de budget initial alloué au début de l'année. Le pourcentage, en orange, est la proportion des montants budget initiaux de chaque niveau sur le montant total des budgets initiaux.
  • Montant budget initial / Montant Total du budget Initial = Pourcentage du Budget Total Initial

Les dimensions: Les dimensions sont les axes d'analyse que vous choisissez pour explorer de façon dynamique vos mesures. Ici nous avons choisi d'analyser les mesures selon les dimensions suivantes:
  • de l'année fiscale, de la période financière, du niveau 5 du code de type de budget, de la fréquence en segment
  • de l'unité administrative niveau 3 en lignes
Rapport PowerPivot avec deux niveaux d'agrégation


Pour permettre ce genre de rapport, il faut utiliser des formules propre à PowerPivot. Le DAX sert à créer des calculs à l'intérieur de rapports, tableaux croisés et tableaux de bord.

Pour comprendre le calcul, imaginez que le rapport comporterait une colonne supplémentaire dans laquelle se retrouverait, sur chaque enregistrement, la valeur constante du total général du montant Budget Initial.

Ensuite vous appliquez le calcul de proportion pour chaque enregistrement. Vous obtiendrez ceci.

Rapport PowerPivot avec deux niveaux d'agrégation en détail


Dans cette figure, vous pouvez observer que l'utilisateur a sélectionné une année, plusieurs périodes, un code type de budget et une fréquence pour filtrer les informations. Le résultat obtenu est : demander à PowerPivot d'effectuer les calculs de proportions nécessaires sur la somme des montants budgétaires initiaux pour chacune des unités administratives pour l'année financière 2010-2011.  Mais comment utiliser le langage DAX pour effectuer ce calcul.

L'utilisation des mesures explicites avec PowerPivot vient régler ce cas.  Le calcul de la proportion s'effectue pour chacune des lignes en fonction de l'unité administrative choisie; il faut définir une nouvelle mesure explicite, où chaque ligne des montants initiaux pour l'unité administrative se comptabilise en fonction des filtres utilisés et du montant du budget total initial.

Montant du budget Initial / Montant Total Budget Initial = Pourcentage du budget total initial

Que l'utilisateur sélectionne une ou plusieurs autres années, périodes, code de type ou encore un ou plusieurs autres unités administratives, le tableau croisé dynamique s'ajustera et effectuera ses calculs en fonctions des filtres sélectionnés.

Si vous voulez une formation sur PowerPivot pour Excel, vous pouvez visiter notre site :


Panorama Technologies
Spécialiste en BI et tableau de bord

2 commentaires: