Matrix - Sorting and Ranking

[V_Jith]

Hi Peeps! I have a Matrix. I would like to implement 2 requirement from my client. 

1. The Week Ending Column on top Should always display the latest Week ending 1st. i.e, 28-06-2025 ; 21-06-2025; 14-06-2025; 07-06-2025; 31-05-2025....

2. I need to add one more column at the starting that gives the Rank, ie, If the maximum collected organisation should be 1 and next best 2, then 3.... But the numbering 1,2,3.. should start from beginning for each Category - MA, NJ AL...

DAX I am using are,

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")

EG:

[v-karpurapud]

Hi @V_Jith 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved?If not, please provide detailed information so we can better assist you.


Thank You.

[v-karpurapud]

Hi @V_Jith 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.


Thank You.

[v-karpurapud]

Hi @V_Jith 

We have not yet heard back from you about whether our response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud · Accepted solution]

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.

[rajendraongole1]

Hi @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.