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 User- define a numerical calculation for the values behind "best" and "worst"
- calculate that value for each project
- find the maximum and minimum value
- identify the projects that match one of the two values (note there can be multiple projects!)
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hello lbendlin ,
thank you for your reply. Please see below some sample data to get started.
Below are the 4 tables you need with the minimum amount of information required to replicate the issue. I have taken 3 random weeks and 3 random projects within these weeks. The projects are found in the table 'Opdracht', their revenue is in table 'Opbrengsten' and their costs are in table 'Kosten'. Besides these three there should also be a seperate Date table, it only needs all the dates in 2025 to work. The Data model is also shown in a screenshot below.
The information in these tables is also enough to copy paste the DAX formulas shared before.
To recreate the issue, simply create a new page and add a table with the following data:
'Opdracht'[Projectnummer] - 'Opdracht'[Opleveringsdatum] - [Project omzet] - [Brutomarge] - [Brutomarge %] - 'Opdracht'[Korte tekst]
Now add a slicer using 'Opdracht'[Opleveringsdatum] and filter on one of the three dates. A screenshot below with an example for you to check if the data is the same:
This data is 100% correct.
Now add a new page with the same table, but add a slicer using the Date column in the date table. Now select the same day, for example 15-12-2025 and you will see the results are vastly different. Same projects, but different numbers.
This is exactly the problem i am trying to solve. Thanks again for taking the time for my issue...
Data model:
Table Opdracht:
| Projectnummer | Opleveringsdatum | Boekdatum | DimOpdrachtId | Korte Tekst |
| 251861 | 15-12-2025 | 19-11-2025 | 1-OP251861.1 | Werkplaats alino, CVI Lelystad |
| 252256 | 15-12-2025 | 8-12-2025 | 1-OP252256.1 | SAR - Coatingvloer Waalwijk |
| 252200 | 15-12-2025 | 19-11-2025 | 1-OP252200.1 | Unit Eindhoven - opslag + serverruimte |
| 251779 | 8-12-2025 | 26-9-2025 | 1-OP251779.1 | Marel Poultry BV Boxmeer - proefkeuken |
| 252187 | 8-12-2025 | 17-11-2025 | 1-OP252187.1 | Alexander poort Capelle a/d IJssel |
| 250869 | 8-12-2025 | 23-10-2025 | 1-OP250869.1 | Sporthal Grote Beek Eindhoven |
| 241914 | 16-6-2025 | 6-6-2025 | 1-OP241914.1 | VVE Bellevue buitentrap |
| 242221 | 16-6-2025 | 26-5-2025 | 1-OP242221.1 | StayOkey Hostel Eindhoven |
| 230624 | 16-6-2025 | 28-5-2025 | 1-OP230624.1 | Kamp Coating, Vianen |
Table Opbrengsten:
| zBedrag | DimOpdrachtId | Boekdatum |
| 1608 | 1-OP251861.1 | 15-12-2025 |
| 201,9 | 1-OP251861.1 | 15-12-2025 |
| 520 | 1-OP251861.1 | 15-12-2025 |
| 0 | 1-OP252256.1 | 15-12-2025 |
| 0 | 1-OP252256.1 | 15-12-2025 |
| 1925 | 1-OP252256.1 | 15-12-2025 |
| 0 | 1-OP252200.1 | 15-12-2025 |
| 2100 | 1-OP252200.1 | 15-12-2025 |
| 4434,66 | 1-OP251779.1 | 8-12-2025 |
| 1383,47 | 1-OP251779.1 | 8-12-2025 |
| 9,6 | 1-OP251779.1 | 8-12-2025 |
| 2770 | 1-OP251779.1 | 8-12-2025 |
| 4794 | 1-OP252187.1 | 8-12-2025 |
| 14100 | 1-OP252187.1 | 26-11-2025 |
| 11844 | 1-OP252187.1 | 1-12-2025 |
| 2829,5 | 1-OP252187.1 | 8-12-2025 |
| 4202,73 | 1-OP250869.1 | 8-12-2025 |
| 152,08 | 1-OP250869.1 | 8-12-2025 |
| € 4.333,00 | 1-OP241914.1 | 19-6-2025 |
| € 10.224,93 | 1-OP242221.1 | 16-6-2025 |
| € 4.460,50 | 1-OP230624.1 | 16-6-2025 |
Table kosten:
| DimOpdrachtId | Boekdatum | zBedrag |
| 1-OP252256.1 | 12-12-2025 00:00 | 569,72 |
| 1-OP252256.1 | 11-12-2025 00:00 | 1263,35 |
| 1-OP252256.1 | 15-12-2025 00:00 | -402,92 |
| 1-OP252200.1 | 30-12-2025 00:00 | 152,8 |
| 1-OP252200.1 | 12-12-2025 00:00 | 20 |
| 1-OP252200.1 | 11-12-2025 00:00 | 20 |
| 1-OP252200.1 | 10-12-2025 00:00 | 20 |
| 1-OP252200.1 | 9-12-2025 00:00 | 20 |
| 1-OP252200.1 | 15-12-2025 00:00 | 1120 |
| 1-OP252200.1 | 8-12-2025 00:00 | 313,56 |
| 1-OP252200.1 | 23-12-2025 00:00 | 128,81 |
| 1-OP251779.1 | 1-12-2025 00:00 | 529,01 |
| 1-OP251779.1 | 3-12-2025 00:00 | 388,98 |
| 1-OP251779.1 | 5-12-2025 00:00 | 862,57 |
| 1-OP251779.1 | 4-12-2025 00:00 | 395,07 |
| 1-OP251779.1 | 25-11-2025 00:00 | 2308,64 |
| 1-OP251779.1 | 8-12-2025 00:00 | -1509,49 |
| 1-OP252187.1 | 19-11-2025 00:00 | 20 |
| 1-OP252187.1 | 20-11-2025 00:00 | 1148,93 |
| 1-OP252187.1 | 5-12-2025 00:00 | 93,5 |
| 1-OP252187.1 | 26-11-2025 00:00 | 1527,68 |
| 1-OP252187.1 | 24-11-2025 00:00 | 886,33 |
| 1-OP252187.1 | 27-11-2025 00:00 | 1408,94 |
| 1-OP252187.1 | 25-11-2025 00:00 | 3103,47 |
| 1-OP252187.1 | 21-11-2025 00:00 | 4047,8 |
| 1-OP252187.1 | 18-11-2025 00:00 | 312,3 |
| 1-OP252187.1 | 4-12-2025 00:00 | 0 |
| 1-OP252187.1 | 17-11-2025 00:00 | 2706,9 |
| 1-OP252187.1 | 3-12-2025 00:00 | 0 |
| 1-OP252187.1 | 2-12-2025 00:00 | 742,18 |
| 1-OP252187.1 | 17-12-2025 00:00 | 450 |
| 1-OP252187.1 | 1-12-2025 00:00 | 2262,51 |
| 1-OP252187.1 | 28-11-2025 00:00 | 450 |
| 1-OP252187.1 | 9-12-2025 00:00 | 2500 |
| 1-OP252187.1 | 14-11-2025 00:00 | 2704,85 |
| 1-OP252187.1 | 8-12-2025 00:00 | -1460,03 |
| 1-OP252187.1 | 22-11-2025 00:00 | -386,2 |
| 1-OP252187.1 | 23-12-2025 00:00 | 3250 |
| 1-OP250869.1 | 5-12-2025 00:00 | 20 |
| 1-OP250869.1 | 12-11-2025 00:00 | 79,82 |
| 1-OP250869.1 | 2-12-2025 00:00 | 0 |
| 1-OP250869.1 | 1-12-2025 00:00 | 110,12 |
| 1-OP250869.1 | 6-12-2025 00:00 | 0 |
| 1-OP250869.1 | 4-12-2025 00:00 | 1850 |
| 1-OP250869.1 | 8-12-2025 00:00 | 280 |
| 1-OP250869.1 | 10-11-2025 00:00 | 471,15 |
| 1-OP250869.1 | 1-10-2025 00:00 | 23,74 |
| 1-OP250869.1 | 17-11-2025 00:00 | 1008,85 |
| 1-OP241914.1 | 10-6-2025 00:00 | 2057,49 |
| 1-OP241914.1 | 11-6-2025 00:00 | -599,5 |
| 1-OP241914.1 | 16-6-2025 00:00 | 960 |
| 1-OP241914.1 | 12-6-2025 00:00 | -632,72 |
| 1-OP242221.1 | 29-5-2025 00:00 | 40 |
| 1-OP242221.1 | 30-5-2025 00:00 | 20 |
| 1-OP242221.1 | 2-6-2025 00:00 | 1629,52 |
| 1-OP242221.1 | 3-6-2025 00:00 | 10 |
| 1-OP242221.1 | 31-5-2025 00:00 | 51,36 |
| 1-OP242221.1 | 1-6-2025 00:00 | 43,8 |
| 1-OP242221.1 | 9-6-2025 00:00 | 360 |
| 1-OP242221.1 | 27-5-2025 00:00 | 1499,91 |
| 1-OP242221.1 | 28-5-2025 00:00 | 2075,9 |
| 1-OP242221.1 | 10-6-2025 00:00 | -368,9 |
| 1-OP242221.1 | 5-6-2025 00:00 | -379,72 |
| 1-OP230624.1 | 30-5-2025 00:00 | 931,67 |
| 1-OP230624.1 | 5-6-2025 00:00 | 1852,32 |
Hi o0llied2 ,
Try using this DAX functionBeste 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 youHi 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.