Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Bonjour,
Je suis bloquée sur un problème et j'ai besoin de vos lumières pour m'en sortir 🙂
J'ai une table qui a pour clé : code_commande + code_position. Une commande peut avoir plusieurs positions (=lignes de commande).
Une commande est faite par un service.
Mon but est d'afficher le top 2 des commandes par service par mois sélectionné --> ça j'y arrive.
Et de faire la même chose en year-to-date, c'est à dire afficher le top 2 pour l'année sélectionnée jusqu'au mois maximum sélectionné --> c'est là que je suis bloquée 😞
Voici un exemple de jeu de données pour que vous compreniez mieux :
Code_commande | code_position | service | idfournisseur | objet_commande | date_creation_cde | periode | montant_position_eur | montant commande total | rank pour la periode par service |
1 | 1 | AT | F1 | xxx | 01/01/20 | 2020-01 | 50 | 1100 | 1 |
1 | 2 | AT | F1 | xcyxc | 01/01/20 | 2020-01 | 900 | ||
1 | 3 | AT | F1 | x | 01/01/20 | 2020-01 | 150 | ||
2 | 1 | GI | F5 | a | 03/01/20 | 2020-01 | 10 | 10 | 5 |
3 | 1 | GI | F1 | dvc | 10/01/20 | 2020-01 | 1000 | 1250 | 2 |
3 | 2 | GI | F1 | sdvf | 10/01/20 | 2020-01 | 250 | ||
4 | 1 | AT | F2 | … | 15/01/20 | 2020-01 | 80 | 180 | 2 |
4 | 2 | AT | F2 | … | 15/01/20 | 2020-01 | 100 | ||
5 | 1 | AT | F5 | … | 16/01/20 | 2020-01 | 150 | 150 | 3 |
6 | 1 | GI | F10 | … | 17/01/20 | 2020-01 | 25 | 25 | 4 |
7 | 1 | GI | F12 | … | 18/01/20 | 2020-01 | 1005 | 1005 | 3 |
8 | 1 | GI | F3 | … | 19/01/20 | 2020-01 | 1500 | 1500 | 1 |
9 | 1 | GI | F3 | sdf | 15/02/20 | 2020-02 | 30 | 200 | 3 |
9 | 2 | GI | F3 | sdf | 15/02/20 | 2020-02 | 40 | ||
9 | 3 | GI | F3 | sdf | 15/02/20 | 2020-02 | 60 | ||
9 | 4 | GI | F3 | sdfs | 15/02/20 | 2020-02 | 45 | ||
9 | 5 | GI | F3 | dfsd | 15/02/20 | 2020-02 | 25 | ||
10 | 1 | AT | F12 | f | 17/02/20 | 2020-02 | 1200 | 1200 | 1 |
11 | GI | F10 | 18/02/20 | 2020-02 | 1300 | 1300 | 1 | ||
12 | GI | F6 | 19/02/20 | 2020-02 | 350 | 350 | 2 | ||
13 | AT | F1 | 20/02/20 | 2020-02 | 27 | 60 | 4 | ||
13 | AT | F1 | 20/02/20 | 2020-02 | 33 | ||||
14 | AT | F2 | 22/02/20 | 2020-02 | 100 | 100 | 3 | ||
15 | AT | F5 | 23/02/20 | 2020-02 | 400 | 400 | 2 |
Pour le top 2 par service en vue mensuelle, j'arrive à obtenir cela :
vue mensuelle | ||||
top 2 des commandes par service | ||||
période sélectionnée : | code_commande | service | montant_commande | rank par service |
2020-01 | 8 | GI | 1500 | 1 |
3 | GI | 1250 | 2 | |
1 | AT | 1100 | 1 | |
4 | AT | 180 | 2 | |
2020-02 | code_commande | service | montant_commande | rank par service |
11 | GI | 1300 | 1 | |
10 | AT | 1200 | 1 | |
15 | AT | 400 | 2 | |
12 | GI | 350 | 2 |
Pour cela, je fais une table calculée de cette manière :
=SUMMARIZECOLUMNS(Factcommandes[Service]; DimFournisseur[nomfournisseur];Factcommandes[Code_commande];Factcommandes[objet_commande];Factcommandes[periode]; "Valeur Nette cde";sum(Factcommandes[montant_position_EUR]) )
et je créé une colonne pour calculer le rank par periode, par service :
=RANKX(
Filter (
TableSummarize;
TableSummarize[Service]=EARLIER(TableSummarize[Service])
&&
TableSummarize[periode]=EARLIER(TableSummarize[periode])
)
;TableSummarize[Valeur Nette cde]
;
;DESC
;Dense
)
Ensuite, dans mon rapport, je filtre rank <=5. ça fonctionne parfaitement.
Maintenant, là où je n'y arrive pas, c'est pour faire la même chose en vue YTD.
Je veux obtenir ça :
vue YTD | |||||
top 2 des commandes par service | |||||
période sélectionnée : | code_commande | service | montant_commande | rank YTD par service | |
2020-01 | 8 | GI | 1500 | 1 | résultat YTD identique à la vue mensuelle de 2020-01 |
3 | GI | 1250 | 2 | ||
1 | AT | 1100 | 1 | ||
4 | AT | 180 | 2 | ||
2020-02 | code_commande | service | montant_commande | rank YTD par service | mois de la commande |
8 | GI | 1500 | 1 | janvier | |
11 | GI | 1300 | 2 | février | |
10 | AT | 1200 | 1 | février | |
1 | AT | 1100 | 2 | janvier |
Avez-vous des idées pour y parvenir ?
Merci beaucoup, AnneSo
Solved! Go to Solution.
@Anonymous
The tricky part is that you want to do the ranking over the whole YTD period, not only the cumulative sum. To follow on your initial approach, you can create this calculated table:
CumulativeTable =
GENERATE (
SELECTCOLUMNS (
DISTINCT ( Factcommandes[periode] ),
"BasePeriode", Factcommandes[periode]
),
VAR baseT_ =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
Factcommandes,
Factcommandes[Service],
Factcommandes[Code_commande],
Factcommandes[periode]
),
"CUMULATIVE Valeur Nette cde",
CALCULATE (
SUM ( Factcommandes[montant_position_EUR] ),
ALLEXCEPT (
Factcommandes,
Factcommandes[service],
Factcommandes[Code_commande]
),
FILTER (
ALL ( Factcommandes[periode] ),
Factcommandes[periode] <= MAX ( Factcommandes[periode] )
)
)
),
Factcommandes[periode] <= EARLIER ( [BasePeriode] )
&& YEAR ( Factcommandes[periode] ) = YEAR ( EARLIER ( [BasePeriode] ) )
)
VAR addRankT_ =
ADDCOLUMNS (
baseT_,
"CumulativeRank_",
RANKX (
FILTER ( baseT_, [Service] = EARLIER ( [Service] ) ),
[CUMULATIVE Valeur Nette cde],
,
DESC,
DENSE
)
)
RETURN
addRankT_
)
Then you can add to the visual BasePeriode (newly created) and the other columns you already had. You can also set a slicer on basePeriode; in this case, you probably do not need it on the visual. Another option would be to do all this with measures rather than through calculated tables. It'd be more flexible and probably easier.
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Oh my god, you rockkkkkkkkk!!!!!!!!
I was really hopeless when posting this yesterday on the forum, quite complex for anyone but you did it!!!!
I just had to change a small thing, to calculate the rank, it wasn't calculated by service, I have put this formula instead in a separated column :
= RANKX(
Filter (
CumulativeTable;
CumulativeTable[Service]=EARLIER(CumulativeTable[Service])
&&
CumulativeTable[BasePeriode]=EARLIER(CumulativeTable[BasePeriode])
)
;CumulativeTable[CUMULATIVE Valeur Nette cde]
;
;DESC
;Dense
)
But again, whaouuuuuuuuuuuuuuu!!! Thanks a million AIB, you make my day (and even my week ^^ )!
Hi,
Yes, DimFournisseur is built on a classic manner :
id | code_fournisseur | country | Nomfournisseur | datedebValidite | datefinvalidite | activeFlag
@Anonymous
The tricky part is that you want to do the ranking over the whole YTD period, not only the cumulative sum. To follow on your initial approach, you can create this calculated table:
CumulativeTable =
GENERATE (
SELECTCOLUMNS (
DISTINCT ( Factcommandes[periode] ),
"BasePeriode", Factcommandes[periode]
),
VAR baseT_ =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
Factcommandes,
Factcommandes[Service],
Factcommandes[Code_commande],
Factcommandes[periode]
),
"CUMULATIVE Valeur Nette cde",
CALCULATE (
SUM ( Factcommandes[montant_position_EUR] ),
ALLEXCEPT (
Factcommandes,
Factcommandes[service],
Factcommandes[Code_commande]
),
FILTER (
ALL ( Factcommandes[periode] ),
Factcommandes[periode] <= MAX ( Factcommandes[periode] )
)
)
),
Factcommandes[periode] <= EARLIER ( [BasePeriode] )
&& YEAR ( Factcommandes[periode] ) = YEAR ( EARLIER ( [BasePeriode] ) )
)
VAR addRankT_ =
ADDCOLUMNS (
baseT_,
"CumulativeRank_",
RANKX (
FILTER ( baseT_, [Service] = EARLIER ( [Service] ) ),
[CUMULATIVE Valeur Nette cde],
,
DESC,
DENSE
)
)
RETURN
addRankT_
)
Then you can add to the visual BasePeriode (newly created) and the other columns you already had. You can also set a slicer on basePeriode; in this case, you probably do not need it on the visual. Another option would be to do all this with measures rather than through calculated tables. It'd be more flexible and probably easier.
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Bonjour @Anonymous
Can you please share the contents of the DimFournisseur table as well?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |