Sorting of values per month per group in a matrix

[DaxPadawan]

Hello! This is actually related to a previous question. 

This is how the data looks. 

Category	Type	Score	Month	Ranking
A	1	100	Jan 2021	1
A	2	50	Jan 2021	2
B	3	75	Jan 2021	1
B	1	80	Feb 2021	2
B	3	90	Feb 2021	1
C	2	60	Feb 2021	1
C	3	55	Mar 2021	1
C	1	40	Mar 2021	2

Now, I put the data into a matrix with the Category and Type as rows, Month as the columns, and Score/Ranking as the values (I plan to use a field parameter to switch between the two). It looks like this: 

Category	Type	Jan 2021	Feb 2021	Mar 2021
A	1	100
	2	50
B	1		80
	3	75	90
C	1			55
	2		60
	3			40

What I want to do now is to be able to sort by Score (or Rank, but that's essentially the same thing) for a specific month. For example, if I want to sort by February:

Category	Type	Jan 2021	Feb 2021 (sort)	Mar 2021
A	1	100
	2	50
B	3	75	90
	1		80
C	2		60
	1			55
	3			40

If it is relevant, Score is a regular column while Ranking a measure. Category, Type, and Month are regular columns as well. Thank you in advance!

[v-yadongf-msft · Accepted solution]

Hi @DaxPadawan ,

Please create two measures:

_Score = 
VAR _sum =
    CALCULATE ( SUM ( 'Table'[Score] ), FILTER ( 'Table', MONTH ( [Month] ) = 2 ) )
RETURN
    IF ( ISINSCOPE ( 'Table'[Month] ), SUM ( 'Table'[Score] ), _sum )



_Rank = 
VAR _rank =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            [Category] = SELECTEDVALUE ( 'Table'[Category] )
                && [Month] = SELECTEDVALUE ( 'Table'[Month] )
        ),
        CALCULATE ( SUM ( [Score] ) ),
        ,
        DESC,
        DENSE
    )
VAR feb_rank =
    MAXX ( FILTER ( 'Table', MONTH ( 'Table'[Month] ) = 2 ), _rank )
RETURN
    IF ( ISINSCOPE ( 'Table'[Month] ), _rank, feb_rank )

Create a field parameter:

You will see:

You can sort by February:

If you don't want to show column subtotals, you can switch off subtotals after sorting:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DaxPadawan]

Sorry @amitchandak, I'm not sure what you mean. 

[amitchandak]

@DaxPadawan , if this a matrix visual, then you sort on the row total on values or on row columns