Ordre de résolution entre Eléments calculés et Champs calculés Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
C
Catab
Membre habitué
Membre habitué
Messages : 57
Inscrit le : 21 avril 2017
Version d'Excel : 2016

Message par Catab » 17 septembre 2019, 09:51

Bonjour,

Dans un tableau croisé dynamique, j'ai à la fois un champ calculé (calcul de la marge en pourcentage) et un élément calculé (différence entre deux années).
Dans cet élément calculé, j'aimerais que la valeur du champ "Marge %" soit la différence entre la valeur "Marge %" de l'année 2019 et celle de 2018.
Or ce n'est pas le cas. La valeur "Marge %" dans mon élément calculé est en fait le quotient entre la différence de marge (€) entre 2019 et 2018 par la différence de chiffre d'affaire entre 2019 et 2018.

Avec un exemple c'est plus clair (fichier en pièce jointe) :

Image

En d'autres termes, mon champ calculé est calculé APRES mon élément calculé.
Est-il possible de modifier cet ordre de résolution dans un TCD ?
Sinon y a-t-il une alternative "élégante" au problème ?

Merci d'avance pour votre aide.

Bàv,
Ordre de résolution Elément calculé et champ calculé.xlsx
(17.49 Kio) Téléchargé 5 fois
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 4'121
Appréciations reçues : 299
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 17 septembre 2019, 19:14

Bonjour

Les éléments calculés posent pas mal de problème notamment de calcul des sous-totaux et totaux

Soit utiliser le calcul de différence intégré et masquer sue 2018 les 4 colonnes vides, soit faire le calcul par PowerQuery intégré à ta version.
Ordre de résolution Elément calculé et champ calculé_2.xlsx
(17.24 Kio) Téléchargé 3 fois
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
C
Catab
Membre habitué
Membre habitué
Messages : 57
Inscrit le : 21 avril 2017
Version d'Excel : 2016

Message par Catab » 18 septembre 2019, 08:52

Bonjour,

Merci Chris pour ta réponse.
Je ne connaissais pas cette façon de faire (la différence intégrée). C'est pas mal du tout, car, en réalité, j'ai plus de deux années dans mon tableau et j'aimerais que celui-ci s'adapte automatiquement lorsque je passe à l'année suivante. Cette méthode vient résoudre ces deux problèmes en plus de me donner la différence de marge % que je souhaite.

Ce n'est pas la première fois que j'entends parler de PowerQuery mais malgré quelques recherches c'est toujours assez flou. L'outil semble intéressant pour moi en tant que Data Manager. Peux-tu m'en dire un peu plus, notamment sur la façon de l'utiliser dans ce cas précis ?
Je dispose de la version Excel 2016 Famille et Petite Entreprise (version 1908).

Merci.

Bàt,
C
Catab
Membre habitué
Membre habitué
Messages : 57
Inscrit le : 21 avril 2017
Version d'Excel : 2016

Message par Catab » 18 septembre 2019, 09:30

PS : Après avoir mis en place cette solution sur mon tableau je constate quelques imperfections... La première étant que je ne peux plus trier mes lignes sur mes champs.
- Tri sur CA total descendant
- Tri sur la différence de CA entre 2019 et 2018 ascendant
Ces tris sont importants.

Le message d'erreur suivant apparait :

"Vous ne pouvez pas utiliser le tri automatique et l'affichage automatique avec des calculs personnalisés contenant des références de position. Voulez-vous désactiver le tri automatique et l'affichage automatique ?"

Moins grave mais un peu embêtant pour le lecteur, les valeurs d'écarts sont sous la même colonne "Année" que les sommes. J'ai changé les libellés des colonnes "Année" ainsi "2019 et Ecart 2018"... L'élément calculé avait l'avantage de grouper ensemble les valeurs d'écart sous un libellé propre.

Je ne sais pas si ces soucis sont solvables... ?

Merci.

Bàv,
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 4'121
Appréciations reçues : 299
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 18 septembre 2019, 12:50

Bonjour

Une solution PowerQuery qui devrait répondre à tout cela
EvolutionsAnnuelles.xlsx
(26.7 Kio) Téléchargé 5 fois
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
C
Catab
Membre habitué
Membre habitué
Messages : 57
Inscrit le : 21 avril 2017
Version d'Excel : 2016

Message par Catab » 18 septembre 2019, 15:03

Je me disais bien que j'avais déjà évoqué ce problème sur ce même forum.

Le sujet date un peu mais : Différence entre deux colonnes d'un TCD

On y retrouve les mêmes interrogations et constats.
En résumé :
- Les éléments calculés posent problèmes pour le calcul des sous-totaux des champs calculés.
- Les éléments calculés sont très lourds lorsque le TCD porte sur un grand nombre d'éléments dans les lignes.
- Le calcul de la différence dans le paramétrage du champ de valeurs empêche le tri de mes étiquettes de lignes par un champ de valeurs.

J'ai trouvé une solution peu élégante, qui demande quelques manipulations et qui est plus sensible aux erreurs mais qui convient à mes besoins.
Séparer les quantités, chiffres d'affaire et marge de chaque année dans des colonnes distinctes dans mon tableau source.
Par la suite, je peux user de champs calculés pour calculer mes marges en % et mes différences.

PS : Mais ça c'était avant de voir ta solution PowerQuery... C'est parfait ::o Mais je ne vois pas comment tu es arrivé à ce résultat, peux-tu me dévoiler tes secrets ? :lole:
Ordre de résolution Elément calculé et champ calculé.xlsx
(17.24 Kio) Téléchargé 1 fois
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 4'121
Appréciations reçues : 299
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 18 septembre 2019, 17:19

Re

Tu peux voir les requêtes en passant par Données, Requêtes et connexions

Il y a 4 requêtes dont tu peux voir les étapes dans le volet de droite :
  • Ventes qui somme par année et article les colonnes Qté, CA, Marge puis calcule le % marge
  • Ecarts0 qui décroise les colonnes de valeurs de Ventes et ajoute l'année N-1
  • Ecarts1 qui
    • fusionne Ecarts0 avec elle-même en récupérant les valeur de l'année N-1 en face de l'année N
    • filtre la 1ère année puisqu'elle n'a pas de N-1
    • calcule l'écart N - N-1
    • Ajoute une colonne concaténant "Ecart" et l'année N pour remplacer Année
    • Supprime les colonnes inutiles (on ne garde que les valeurs d'écarts pour chaque couple Année, Article)
    • Recroise pour retrouver les colonnes Qté, CA et les 2 Marge
  • Final qui fait l'union de Ventes et Ecarts1
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
C
Catab
Membre habitué
Membre habitué
Messages : 57
Inscrit le : 21 avril 2017
Version d'Excel : 2016

Message par Catab » 19 septembre 2019, 13:29

Re 78chris,

Merci. Je découvre là tout un pan d'Excel que je ne connaissais pas.
L'outil a l'air puissant :)

Je te remercie pour ton aide. Je vais clôturer le sujet.

Bàt,
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message