Calculate the sum of rankx values within a category, across all categories

[abbaig63]

Hello,

I am trying to rank a list of stores by sales on a period by period basis. For example, this is what a desired table would look like:

Period	Store	Sales	Rank
1	A	100	2
1	B	150	1
1	C	75	3
2	A	110	3
2	B	120	2
2	C	135	1

Then I would like have the sum of the ranks for each store like so:

Store	Sum of Rank
A	5
B	3
C	4

I am able to calculate the ranks in the first table using the following measure:

Rank = 

    RANKX(

        FILTER(

            ALL(Table[Period], Table[Store])

            Table[Period] = MAX(Table[Period])

        ),

        CALCULATE(SUM(Table[Sales])

    )

However, this requires the period being included, which I do not need, since I only care about the final sum.

How can I apply this measure across all periods independently?

Thank you

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure what is your desired outcome, but I tried to create it by using measures.

Please check the below picture and the attached pbix file.

It is for creating a measure.

Expected result measure: =
VAR _rank =
    ADDCOLUMNS (
        VALUES ( Period[Period] ),
        "@rank",
            RANK (
                SKIP,
                SUMMARIZE ( ALL ( Data ), Store[Store], Period[Period] ),
                ORDERBY ( CALCULATE ( SUM ( Data[Sales] ) ), DESC ),
                PARTITIONBY ( Period[Period] )
            )
    )
RETURN
    SUMX ( _rank, [@rank] )

[abbaig63]

This works perfectly, thank you!