Soustraction et tableau croisé dynamique

**denissmile** · 06/08/2019, 02h39

Bonsoir de Montréal,

J'ai créé un tableau croisé dynamique financier. Je tente de soustraire Budgété_PR - Budgété_PS. Cela fonctionne en mode mois. Cependant aussitôt que j'insère les champs trimestre, rien n'y fait. On me demande de dissocier ces derniers. Quant je dissocie, ce sont les date qui apparaisses et je peux effectuer la soustraction. À ce point je peux grouper si je le veux. Cependant, si je dissocie et applique la soustraction, impossible de grouper.

De même quand je suis en mode trimestre il me refuse la soustraction (doit dissocier).

Une solution?

Nom : excel.png
Affichages : 1957
Taille : 50,3 Ko

Nom : excel.png
Affichages : 1957
Taille : 50,3 Ko

**78chris** · 06/08/2019, 07h30

Bonjour

La soustraction se fait par éléments calculés. Les éléments calculés posent pas mal de problème.

Il faut mettre en amont dans la source Budgété_PS et tous les sous-niveaux en négatif.

Tu as 2016 dont tu peux retraiter ta source avec PowerQuery intégré de façon à conserver la souplesse du TCD

Éventuellement poste un extrait pour qu'on te montre comment faire

**denissmile** · 06/08/2019, 15h53

Qu'entendez-vous par extrait? Le fichier?

**78chris** · 06/08/2019, 16h38

RE

Oui

Allégé et anonymisé si besoin

**denissmile** · 06/08/2019, 17h05

Envoyé par 78chris

RE

Oui

Allégé et anonymisé si besoin

OK j'ai laissé les 2 onglets utile (Prévision et transaction).

J'ai effectivement power query et power BI mais jamais utilisé.

Merci de me montrer comment y arriver.JDS_Revenus-Dépenses_04-08-19.xlsm

**78chris** · 06/08/2019, 17h52

RE

Voir ci-joint fichier Commenté

**denissmile** · 06/08/2019, 23h07

Envoyé par 78chris

RE

Voir ci-joint fichier Commenté

Merci. J'ai quelques questions

1. Se placer dans une cellule du tableau source Données ---> On parle ici de la base de données transaction?)
2. A partir du tableau : ouvre PowerQuery
3. Modifier le type de la colonne date en Date (onglet Accueil) ---> Moi je voie à droite Data comme document et quand j'ouvre je vois les données. Mais comment sélectionner modifier le type de colonne.
4. Supprimer toutes les colonnes inutiles pour le TCD ---> J,ai besoin de la colonne mois. Je crois que j'y arriverai. Clique droit sur la colonne et sélectionner supprimer.
5. Onglet Ajouter une colonne, colonne personnalisée et taper la formule if Text.Contains([Type],"PS") then -[Total] else [Total] ---> Je crois que j'y arriverai.
6. Sortir par Fermer et Charger dans, Connexion seulement ---> à ce stade-ci ???
7. Dans Excel, créer un TCD avec comme source la requête PowerQuery ---> à ce stade-ci ???

**78chris** · 06/08/2019, 23h40

RE

Envoyé par denissmile

Merci. J'ai quelques questions

1. Se placer dans une cellule du tableau source Données ---> On parle ici de la base de données transaction?) OUI
2. A partir du tableau : ouvre PowerQuery
3. Modifier le type de la colonne date en Date (onglet Accueil) ---> Moi je voie à droite Data comme document et quand j'ouvre je vois les données. Mais comment sélectionner modifier le type de colonne.
Sélectionner la colonne et sélectionner le type au milieu de l'onglet Accueil
4. Supprimer toutes les colonnes inutiles pour le TCD ---> J,ai besoin de la colonne mois. Je crois que j'y arriverai. Clique droit sur la colonne et sélectionner supprimer.
Le mois ne sert à rtien : le TCD peut le fournir à partir de la date (comme le trimestre)
Pour les colonnes: on peut en sélectionner plusieurs d'un coup
5. Onglet Ajouter une colonne, colonne personnalisée et taper la formule if Text.Contains([Type],"PS") then -[Total] else [Total] ---> Je crois que j'y arriverai.
6. Sortir par Fermer et Charger dans, Connexion seulement ---> à ce stade-ci ??? Après l’étape 5. Les étapes sont dans l'ordre...
7. Dans Excel, créer un TCD avec comme source la requête PowerQuery ---> à ce stade-ci ???

**denissmile** · 07/08/2019, 01h03

Envoyé par 78chris

RE

Merci je vais essayer tout ça.

Je reviens aux mois. J'ai deux tableaux à créer : Par trimestre et par trimestre/mois. Habituellement avec l'otio +/- dans afficher j'obtient les mois sous trimestre mais dans le fichier que j'ai reçu. Ça ne fonctionne pas. C'est pourquoi je parlais de garder la colonne mois.

**78chris** · 07/08/2019, 01h22

RE

Clic droit sur trimestre dans le TCD que j'ai fait, grouper, ajouter mois : ça c'est de basique TCD indépendant de PowerQuery.

**denissmile** · 07/08/2019, 03h09

Envoyé par 78chris

RE

Clic droit sur trimestre dans le TCD que j'ai fait, grouper, ajouter mois : ça c'est de basique TCD indépendant de PowerQuery.

Bien oui. Merci. Cela ne fonctionnait pas avec l'autre tableau sans Query.

**78chris** · 07/08/2019, 08h34

RE

Si, cela fonctionne dans tous les TCD : il suffit de placer la date dans le TCD puis de grouper comme on le souhaite.

La version 2016 groupe automatiquement par défaut mais on peut adapter le groupement ou dégrouper.

Tu peux donc supprimer Mois du tableau Transaction...

**denissmile** · 07/08/2019, 14h48

Envoyé par 78chris

RE

Si, cela fonctionne dans tous les TCD : il suffit de placer la date dans le TCD puis de grouper comme on le souhaite.

La version 2016 groupe automatiquement par défaut mais on peut adapter le groupement ou dégrouper.

Tu peux donc supprimer Mois du tableau Transaction...

Merci

Ça fonctionne. J'avais juste oublié. TCD est certaines fois plus complexe qu'un tableau normal...

**denissmile** · 07/08/2019, 16h01

Envoyé par 78chris

RE

C'est réussi! Merci

Maintenant si je veux que la présentation du tableau ait la forme suivante :

Bugéter_PR - Budgéter_PS = +/- Total

J'ai essayé
if Text.Contains([Type],"Pr") then -([Type],"Ps") else [Total] ---> Sans succès
if Text.Contains([Type],"PS") then ([Type],"PS")-([Type],"PS") else [Total] ---> Sans succès

y a-t-il une formule pour ça?

**78chris** · 07/08/2019, 18h21

RE

Je ne comprends pas : tu as bien le total en bas du TCD correspondant à la soustraction

PowerQuery calcule par ligne et, sur une même ligne, on a soit l'un soit l'autre qui ne concerne pas les mêmes catégories...

**denissmile** · 07/08/2019, 22h59

Envoyé par 78chris

RE

Je ne comprends pas : tu as bien le total en bas du TCD correspondant à la soustraction

PowerQuery calcule par ligne et, sur une même ligne, on a soit l'un soit l'autre qui ne concerne pas les mêmes catégories...

Merci

Puisque ce tableau représente des prévisions budgétaires, les sous-catégories en PS devraient êtres positives et au bas la résultat du PR - PS, et non par sous-catégories (ex: assurance, fourniture, etc.).

Est-ce plus claire?

**78chris** · 08/08/2019, 11h05

RE

Cela n'est pas possible directement dans un TCD où tout calcul repose sur la notion de contexte : si on détaille les sous-catégorie le calcul se fait par sous catégories.

On peut

Créer une requête supplémentaire
- Dupliquer Data sous le nom de Data0
- dans cette copie supprimer la dernière opération de création de colonne personnalisée
Compléter la requête Data
- Grouper par Date et faire la somme de Montant sous le nom Total (Onglet Transformer, Regrouper Par)
- Ajouter 3 colonne personnalisées :
  - Type avec comme valeur "Solde" (ou autre vocale pour le résultat que tu souhaites)
  - Catégorie avec une chaîne vide (taper "")
  - Sous-catégorie de même
- Permuter les colonnes par Glisser-Déplacer (facultatif)
- Réaliser l'union avec Data0 : Onglet Accueil, Ajouter des requêtes
- Sortir de PowerQuery

Actualiser le TCD : on a un Type supplémentaire affichant l'écart.

**denissmile** · 08/08/2019, 15h23

OK j'ai compris que c'est Query.

J'ai un bizare de phénomène. Je n'ai plus de data. Même si je me positionne dans ma base de données et ouvre power Query???