Forum Discussion

Yikester20's avatar
Yikester20
Regular Visitor
8 years ago
Solved

DAX Adding up ranks over another grouping

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...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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] )

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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] )