Forum Discussion
Determining best or worst project
The issue was not the ranking logic itself, but how the data was being filtered and grouped.
We removed DimProjectId from the calculation
Even though it is the technical key, all reporting is done on Projectnummer.
Including DimProjectId caused the calculation to run at a lower grain, which resulted in the wrong project being selected.
Once we removed it, the correct project was ranked as best/worst.We fixed the date filtering in the margin measures
The active calendar relationship interfered with filtering by Opdracht[Opleveringsdatum].
We solved this by clearing the calendar filter and explicitly applying the date filter to Opleveringsdatum.
After these changes, the correct best and worst projects are selected, and the result is consistent across weeks and years.
Hi o0llied2 ,
Try using this DAX function
Beste Project Nummer2 =
VAR SelectedDates =
VALUES ( Datum[Datum] )
VAR Projects =
FILTER (
ADDCOLUMNS (
Opdracht,
"Omzet", [Project Omzet],
"BrutomargePct", [Brutomarge %]
),
NOT ISBLANK ( Opdracht[Opleveringsdatum] )
&& Opdracht[Opleveringsdatum] IN SelectedDates
&& [Omzet] >= 1000
)
RETURN
IF (
ISEMPTY ( Projects ),
"Geen afgesloten projecten",
MAXX (
TOPN ( 1, Projects, [BrutomargePct], DESC ),
Opdracht[Projectnummer]
)
)
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi Anonymous ,
Thank you for jumping in with a suggestion! I tried it, but unfortunately the same issue persists. The calculation of gross margin in % just does not seem to go correctly because the filtering of the dates is not done properly when using the date filtering from the date calender.
Do you have any other suggestions?
Really appreciate your time and effort.