Power Pivot : colonne calculée sur Tableau Croisé du modèle ?
Bonjour,
Je fais mes premiers pas avec Power Pivot (Excel 2016), et je souhaite ajouter une colonne calculée dans le tableau croisé dynamique du modèle, mais la fonction champ calculé n’est pas accessible dans le menu.
J’en ai déduit que ce genre de calcul est à intégrer dans une table ou avec une mesure DAX, mais je n’ai pas réussi à trouver la solution.
Le diagramme Power Pivot est le suivant :
Voici la Table des opérations du système comptable (ERP) :
et la Table des natures comptables (Nature):
Ce qui donne le tableau croisé ci-dessous, dans lequel je cherche à ajouter une colonne calculant la différence entre le total Recettes et le total Dépenses :
le même, avec la vue des champs et requêtes :
Je joins les fichiers, au cas où cela soit utile:
- résultat-agences = fichier incluant le TCD
- export-erp = table des données comptables ERP
- table-natures-comptables = table des correspondances de Nature
Svp quelle serait la mesure DAX à écrire ? et à quel emplacement ?
Merci beaucoup pour votre aide.
Bonjour,
Une solution est de :
- Activer le total général au TCD pour les lignes et les colonnes :
- Mettre les montants "dépenses" en négatifs en ajoutant 2 colonnes calculées à la table ERP :
=RELATED(Nature[Dep/Rec])=IF('ERP'[Dép/Rec]="Dépenses";-'ERP'[Réalisé euros];'ERP'[Réalisé euros])Ce qui donne en résultat :
Le total général = différence entre dépenses et recettes. Est-ce que cela te convient ?
Bonjour,
Tout d'abord merci pour cette réponse et cette proposition. L'approche est astucieuse.
Cependant je ne pourrai pas utiliser cette méthode, car il y a une convention de signe à respecter dans le reporting de mon entreprise : les valeurs des dépenses et des recettes doivent être affichées en positif.
Bien cordialement,
Bonjour,
Dommage...
Une autre solution est de calculer la différence directement à coté du TCD.
Pour se faire il faut aller dans les options Excel => Formules => décocher "Utiliser les fonctions LIRE..."
On y voit que du feu :
Seul problème : il faut agir manuellement.
RE,
Via PowerQuery, au plus simple, requête réalisée 100% avec le Ruban pour plus de simplicité :
Le fichier :
Bonjour,
Merci pour ces réponses, et le temps consacré.
Cette approche Power Query est intéressante et répond au besoin.
Je suis débutant, et j'ai encore du mal à voir dans la requête le mode opératoire de chaque étape avec les commandes du ruban de Power Query.
Par exemple, comment créer la colonne tbl ?
Je vais essayer de reproduire cet enchaînement sur un fichier dupliqué.
Merci encore.
RE,
Il s'agit de fusionner les 2 tables
Avec comme "clés" la colonne "nature" (jointure externe gauche)
Ensuite tu développes la table, en cliquant sur les 2 petites flèches au niveau du nom de colonne
On pivote la colonne "Dép/Rec" pour avoir une colonne Dépenses et une colonne Recettes
On remplace les valeurs "null" par 0 en sélectionnant les 2 colonnes puis en cliquant sur "remplacer les valeurs"
On ajoute ensuite une colonne personnalisée pour faire la différence entre Recettes et Dépenses
Enfin tu sélectionnes les 3 premières colonnes et tu dépivotes les autres colonnes
Je pense que je n'ai rien oublié
Super, merci JB pour ta patience et ces étapes didactiques, je comprends mieux le cheminement.
J'ai reproduit pas à pas, et j'arrive au TCD ci-dessous :
Dans mon TCD, chaque type de nature est contenu dans le champ attribut, alors que le tien respecte bien la répartition des natures entre Dépenses et Recettes (par exemple la masse salariale est forcément en Dépenses, alors qu'elle s'affiche ici en Dépenses, Recettes et Solde).
Désolé, est-ce un problème dans mon TCD, ou bien dans ma requête ?
Peut-être faudrait-il que je supprime les lignes à zéro dans la nouvelle requête ?
De nouveau, un grand merci.
Exact, je n'avais pas fait attention à cette subtilité.
Je t'invite donc à agir directement sur la requête dans PowerQuery en filtrant les valeurs à 0 dans la colonne Valeur.
Tu décoches simplement le 0.
Ca devrait le faire
Et pour ce qui est du solde, on cache les détails en cliquant sur le petit -
Mais ça, je ne t'apprends rien !
C'est parfait !
Merci mille fois pour votre aide et accompagnement.
Cela m'ouvre de nouveaux horizons avec Excel.
Merci du retour !
Effectivement, et rien qu'avec le ruban de PowerQuery on peut faire beaucoup de choses