Thanks for your replies. If im not wrong, these work if both the columns have values.
In my case, I have one column wit Text (Fiscal Period) and then values. I tried to use below DAX, but got the following error -
"A single value for column 'Sort By Fiscal Year' in table 'SFDC' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Any idea how to correct this?
Final Rank = RANKX ( ALL (SFDC), RANKX ( ALL (SFDC), SFDC[Sort By Fiscal Year],, ASC ) + DIVIDE ( RANKX ( ALL (SFDC), SFDC[Value USD],, DESC ), ( COUNTROWS ( ALL (SFDC)) + 1 ) ) )
Please consider adding Sorting Options in the Matrix as those available for the Pivot Table which allows for sorting at EACH level. For example in Excel if you have this Pivot Table setup: Rows: Year, Type, Company and Location Columns: Month Values: Total Revenue (Measure) In a Pivot Table you can sort at each level! (without disrupting the sorting at the other levels) So you can sort your Year descending by Year, Type ascending by Type, Company also ascending by Company and lastly Location descending by Total Revenue. So in Excel you will always be looking at the current Year (on top), followed by Type and Company (A to Z) and only your Locations will be sorted based on Total Revenue best to worst. And by the way all previous years are collapsed only showing annual totals but you can always open a certain year and find what you are looking for. Now if you want to you can go and sort at the Company level but this time descending by Total Revenue (instead of by Company A to Z). Again Year and Type will not be disturbed you will get your current Year (on top) followed by Type (A to Z) and then the Companies will be sorted best to worst based on Total Revenue and the Locations for each Company will also be sorted best to worst based on Total Revenue. None of this is currently possible in the Matrix! Thanks!
EDIT: I didn't mention the Month Column but of course you can sort it also to show the Months either Jan to Dec or Dec to Jan!
For example in beginning of the Year you can sort Jan to Dec but closer to the end you can reverse the order Dec to Jan
so you see the most recent data first (meaning on the left side in the table).