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.
Super Usero0llied2 , Better use the index function, which allows partition by and order by
example
calculate([Meausre], index(1, allselected(Table[Device], Table[Last Update Date]), orderby(Table[Last Update Date],desc),,partitionBy(Table[Device]) ) )
Power BI Index: Top/Bottom Performer by name and value: https://youtu.be/HPhzzCwe10U
Hey Amit !
Thank you for the suggestion, i whave made the following measure to test it but it gives the same result unfortunately, any suggestions:
Beste Project INDEX =
VAR Tabel =
SUMMARIZE(
Opdracht,
Opdracht[DimOpdrachtId],
Opdracht[Projectnummer],
"BrutomargePct", [Brutomarge %]
)
RETURN
CALCULATE(
MAX ( Opdracht[Projectnummer] ),
INDEX(
1,
Tabel,
ORDERBY ( [BrutomargePct], DESC )
)
)
Really appreciate the help!