Forum Discussion
Matrix - Sorting and Ranking
Hi V_Jith
Thank you for contacting the Microsoft Fabric Community Forum.
To enhance the accuracy and clarity of the matrix visualization, I have created a sample .pbix file to illustrate a possible solution. While the structure may not exactly match your dataset, the example demonstrates the logic using calculated columns and DAX to achieve the intended result. Create a new column Week Ending Date Sort by extracting the actual week-ending date from the punch_date field. Then, sort the column using the formatted Week Ending Category label to ensure that matrix columns appear in the correct descending chronological order, regardless of label format. Additionally, update the Rank by Division to rank centers within each division based on total Paid Hours across all weeks, using the REMOVEFILTERS function to remove weekly context so that the rankings reflect aggregated values. I have attached the .pbix file, please take a moment to review it. Let me know if you have any requirements or questions.If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
Super UserHi V_Jith - create a calculated column as below: it returns text column and it cannot sorted.
Week Ending Category =
VAR CurrentDate = 'Data'[punch_date]
VAR WeekdayNum = WEEKDAY(CurrentDate, 1) // 1 = Sunday=1, Monday=2, ..., Saturday=7
VAR DaysToAddToEndOfWeek = 7 - WeekdayNum
VAR WeekEndDate = CurrentDate + DaysToAddToEndOfWeek
RETURN
"Week Ending " & FORMAT(WeekEndDate, "DD-MM-YYYY")
Create a numeric "Week Ending Sort" column in your Date or Data table:
Week Ending Sort =
VAR CurrentDate = 'Data'[punch_date]
VAR WeekdayNum = WEEKDAY(CurrentDate, 1)
VAR DaysToAddToEndOfWeek = 7 - WeekdayNum
RETURN CurrentDate + DaysToAddToEndOfWeek
Now you can sort by Column → Week Ending Sort.
create a measure to get the rank:
Division Rank =
VAR CurrentDivision = MAX('Data'[Division])
VAR CurrentCenter = MAX('Data'[Center])
RETURN
RANKX(
FILTER(
VALUES('Data'[Center]),
CALCULATE(MAX('Data'[Division])) = CurrentDivision
),
CALCULATE(SUM('Data'[Paid Hours])),
,
DESC,
DENSE
)
Replace paid hours with the correct numeric field representing the total hours.
Hope this works. please check and let know.