DAX Adding up ranks over another grouping

[Yikester20]

I have a table with data like this:

StoreID | Week/End | Sales
===========================
1           | 9/2             | 10
2           | 9/2             | 5
3           | 9/2             | 3
1           | 9/9             | 5
2           | 9/9             | 2
3           | 9/9             | 10

What I need to do is rank the sales by week, then add up the value of the ranking.

I tried a measure using RANKX like this:

=RANKX(ALL(Stores), SUMX(RELATEDTABLE(Sales), [Sales]))

Which will give me the following table:

StoreID | Week/End | Sales | Rank
=====================================
1       | 9/2      | 10    | 1
2       | 9/2      | 5     | 2
3       | 9/2      | 3     | 3
1       | 9/9      | 5     | 2
2       | 9/9      | 2     | 3
3       | 9/9      | 10    | 1

What I need to do now is to add up the rank column by Store, so in the end I would get a table like this:

StoreID | Overall Rank
=======================
1       | 3
2       | 5
3       | 4

So store 1 would have a value of 3, because of the w/e 9/2 of 1 and a w/e 9/9 rank of 2. store 2 would have a overall rank of 5, because of the w/e 9/2 of 2 and a w/e 9/9 rank of 3, and so on.

I would try to do this in SQL, but unfortunately some of my base data is in excel and some of it is in SQL, so I need to do this in DAX or M.

Any ideas would be greatly appreciated!

[Eric_Zhang · Accepted solution]

@Yikester20

Try to create a measure as below. See more details in the attached pbix file.

Overall Rank =
VAR tbl =
    ADDCOLUMNS (
        sales,
        "rank", RANKX (
            CALCULATETABLE ( sales, ALLEXCEPT ( sales, sales[Week/End] ) ),
            sales[Sales],
            ,
            DESC,
            DENSE
        )
    )
RETURN
    SUMX ( tbl, [rank] )

[Ashish_Mathur]

Hi,

A simple SUM measure should work

=SUM([Rank])