Microsoft 365 TCD et référence de cellules

Laurent78 · 1 Juillet 2024

Bonjour à Tous,
Savez vous si il est possible dans un TCD de faire une formule qui calcule le ratio d'une ligne par rapport au total d'une autre ligne.
Bien évidement, ce que j'ai écrit n'est vraiment pas clair ...
Donc, le mieux est de jeter un œil au fichier joint et de lire ce qui suit en même temps.
En fait, j'ai une base de données, anonymisée et avec des valeurs aléatoires (donc, les valeurs peuvent paraitre totalement stupides, c'est normal), onglet Datas.

à partir de ces données je fais un un TCD qui me sort les totaux souhaités, total des produits (7) et des charges (6). c'est simple et ça fonctionne.
Mais je souhaiterais avoir une colonne (cf flèche rouge) dont la valeur est le % par rapport au total des produits (7) des comptes de marges bruts.
J'ai mis quelques résultats souhaités dans la colonne, résultats obtenu à la main.

Je ne l'ai pas mis dans l'exemple, pour simplifier, mais en fait, il faudrait cela avec en plus les mois en colonnes.

Voilà, si vous avez une idée...

NB : les données de l'onglets Datas sont en fait extraites via PowerQuery, à la fois d'un serveur SQL et de fichiers Excel eux mêmes étant des exportations. Les datas représentent plusieurs centaines de milliers de lignes.

Merci d'avance pour vos idées,
Laurent

crocrocro · 1 Juillet 2024

Bonjour Laurent, en pj la solution proposée.
Il faut :
insérer un champ calculé (nom Ratio) avec pour formule = montant
et dans Paramètres de champ de la ligne Somme de Ratio -> Afficher les valeurs -> % du total de la ligne parente (voir ci-dessous)

Je suis allé un peu vite : le % est celui par rapport au total du niveau hiérarchique et non du Total Brut comme demandé. lignes 7xx sur Total 7, lignes 6xx sur Total 6 ... et non lignes 6xx sur Total 7

Laurent78 · 1 Juillet 2024

crocrocro à dit:
Bonjour Laurent, en pj la solution proposée.
Il faut :
insérer un champ calculé (nom Ratio) avec pour formule = montant
et dans Paramètres de champ de la ligne Somme de Ratio -> Afficher les valeurs -> % du total de la ligne parente (voir ci-dessous)

Regarde la pièce jointe 1199805

Je suis allé un peu vite : le % est celui par rapport au total du niveau hiérarchique et non du Total Brut comme demandé. lignes 7xx sur Total 7, lignes 6xx sur Total 6 ... et non lignes 6xx sur Total 7

Bonjour,

Merci, mais en effet, ce n'est pas ce que je souhaite.
Mais merci d'avoir essayé.

crocrocro · 2 Juillet 2024

Bonjour Laurent,
une nouvelle proposition en pj (du bricolage hélas, formules gourmandes et pas-tout-automatic).
Quelques explications :
- Une nouvelle feuille Marge Brute Par Client avec un tableau correspondant au cumul par client des total des produits (7) des comptes de marges bruts.
La formule du tableau : =SOMME.SI.ENS(TableauComptes[[#Tout];[Montant]];TableauComptes[[#Tout];[Client]];[@Client];TableauComptes[[#Tout];[Classe]];"7";TableauComptes[[#Tout];[Marge Brute]];"Comptes Marge Brute").
- Dans la feuille Data, ajout de la colonne Total Produit Marge Brute avec la formule =RECHERCHEV([@Client];TableauCumulClientClasse7;2;FAUX)
- Dans le TCD, ajout de cette colonne avec pour type de calcul Max. Cette colonne sera utilisée pour le calcul du ratio (je n'ai pas réussi à partir de champs calculés de faire le ratio dans le TCD, à voir ...). Colonne à masquer. Colonne Ratio, Hors du TCD donc, avec pour formule =SI(C4<>0;B4/C4;"").

Une dernière remarque : je suis parti d'un TCD vierge, il n'y a donc pas ta présentation originale.

alexga78 · 2 Juillet 2024

Bonjour Laurent, le forum,

Etant donné que vos données sont traitées initialement par Power Query, pourquoi ne pas calculer ce % dans PQ et exporter vos données en TCD ?

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau4"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Client"}, {{"All", each _}, {"Sum Marge - 7", each
        List.Sum(Table.SelectRows(_, each ([Marge Brute] = "Comptes Marge Brute") and ([Classe] = "7"))[Montant])}}),
    #"Added Custom" = Table.Combine(Table.AddColumn(#"Grouped Rows", "Custom", each
                      let x= [#"Sum Marge - 7"]
                      in Table.AddColumn([All], "Ratio % par rapport au total 7 marge Brute",
                      each [Montant]/x*100 ))[Custom])
in
    #"Added Custom"

Cordialement.

Laurent78 · 2 Juillet 2024

crocrocro à dit:
Bonjour Laurent,
une nouvelle proposition en pj (du bricolage hélas, formules gourmandes et pas-tout-automatic).
Quelques explications :
- Une nouvelle feuille Marge Brute Par Client avec un tableau correspondant au cumul par client des total des produits (7) des comptes de marges bruts.
La formule du tableau : =SOMME.SI.ENS(TableauComptes[[#Tout];[Montant]];TableauComptes[[#Tout];[Client]];[@Client];TableauComptes[[#Tout];[Classe]];"7";TableauComptes[[#Tout];[Marge Brute]];"Comptes Marge Brute").
- Dans la feuille Data, ajout de la colonne Total Produit Marge Brute avec la formule =RECHERCHEV([@Client];TableauCumulClientClasse7;2;FAUX)
- Dans le TCD, ajout de cette colonne avec pour type de calcul Max. Cette colonne sera utilisée pour le calcul du ratio (je n'ai pas réussi à partir de champs calculés de faire le ratio dans le TCD, à voir ...). Colonne à masquer. Colonne Ratio, Hors du TCD donc, avec pour formule =SI(C4<>0;B4/C4;"").

Une dernière remarque : je suis parti d'un TCD vierge, il n'y a donc pas ta présentation originale.

Regarde la pièce jointe 1199845

Bonjour,

Merci pour les idées. Mais en effet, c'est le calcul du ration dans le TCD qui pose le plus de problème. Et, mettre des formules à côté ne convient pas, le "vrai" TCD ayant de segments de sélection (date, clients, entités societé, etc ...), la recopie ne fonctionne pas.
Merci encore.
Je vais peut-être regarder du côté de PowerPivot et DAX. cela va m'obliger à approfondir le sujet.

Laurent78 · 2 Juillet 2024

alexga78 à dit:

Bonjour Laurent, le forum,

Etant donné que vos données sont traitées initialement par Power Query, pourquoi ne pas calculer ce % dans PQ et exporter vos données en TCD ?

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau4"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Client"}, {{"All", each _}, {"Sum Marge - 7", each
        List.Sum(Table.SelectRows(_, each ([Marge Brute] = "Comptes Marge Brute") and ([Classe] = "7"))[Montant])}}),
    #"Added Custom" = Table.Combine(Table.AddColumn(#"Grouped Rows", "Custom", each
                      let x= [#"Sum Marge - 7"]
                      in Table.AddColumn([All], "Ratio % par rapport au total 7 marge Brute",
                      each [Montant]/x*100 ))[Custom])
in
    #"Added Custom"

Cordialement.

Bonjour,

Merci, je vais tester, mais en fait, j'ai déjà de grosses requêtes dans PowerQuery, et j'avais espérer une astuce dans les TCD.
Cela étant, je vais regarder ce que ta proposition donne en performance.

Je vais peut-être regarder du côté de PowerPivot et DAX. cela va m'obliger à approfondir le sujet.

Merci.

alexga78 · 4 Juillet 2024

Bonjour Laurent, le forum,

Un essai pour optimiser la performance au besoin.

PowerQuery:

let
    fx = (tbl) =>
     [ a = Table.SelectRows(tbl, each ([Marge Brute] = "Comptes Marge Brute") and ([Classe] = "7")),
       b = Table.ToColumns(Table.Group(a, {"Client"}, {{"x", each List.Sum(_[Montant])}})),
       c = Record.FromList(b{1},b{0}),
       d = Table.AddColumn(tbl, "Ratio % - Total 7 marge Brute",
             each try ([Montant] / Record.FieldValues(Record.SelectFields(c,[Client])){0}) otherwise null)
     ][d],
    Result = fx(Excel.CurrentWorkbook(){[Name="Tableau4"]}[Content])
in
    Result

Bonne soirée

Microsoft 365 TCD et référence de cellules

Laurent78

XLDnaute Occasionnel

Pièces jointes

crocrocro

XLDnaute Impliqué

Pièces jointes

Laurent78

XLDnaute Occasionnel

crocrocro

XLDnaute Impliqué

Pièces jointes

alexga78

XLDnaute Occasionnel

Laurent78

XLDnaute Occasionnel

Laurent78

XLDnaute Occasionnel

alexga78

XLDnaute Occasionnel

Discussions similaires