Ranking of values per month per group

[DaxPadawan]

The data is set up as follows, with the ranking that I'd like to see:

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

Basically, I wanted to rank the Types by the Scores, but within each Category and each Month.

I want to visualize it with a matrix, with the Category and Type as the rows and Month as the column. The ranking will be the values. 

Honestly, just really confused about whether or not this needs to be a calculated column or if this can be done as just a measure. I've been trying to use RANKX but no matter what I try, all the ranks are 1.

Thanks in advance y'all! 

[amitchandak · Accepted solution]

@DaxPadawan , if you need static ranking you need a column, you need dynamic raking (usually on the measure) you need ti use measure

Column Rank = rankx(filter(Table, [Category] = earlier([Category])  && , [Month] = earlier([Month])), [Score],,desc, dense)

Meausre Rank

rankx(allselected(Table[Month]), calculate(sum(Table[Score])) ,,desc, dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[v-yanjiang-msft · Accepted solution]

Hi @DaxPadawan ,

According to your description, if you want a calculated column, @amitchandak's solution works fine. If you want a measure, here's my solution.

Measure =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        [Category] = MAX ( [Category] )
            && [Month] = MAX ( [Month] )
    ),
    CALCULATE ( SUM ( [Score] ) ),
    ,
    DESC,
    DENSE
)

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak · Accepted solution]

@DaxPadawan , if you need static ranking you need a column, you need dynamic raking (usually on the measure) you need ti use measure

Column Rank = rankx(filter(Table, [Category] = earlier([Category])  && , [Month] = earlier([Month])), [Score],,desc, dense)

Meausre Rank

rankx(allselected(Table[Month]), calculate(sum(Table[Score])) ,,desc, dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale