Multi level (sub)ranking

DAX DAX Calculations
10

I believe this is what you want: Edna - multilevel ranking.pbix (26.5 KB)

image
image711×171 6.35 KB

Start Date Rank = 
VAR CurrentSelection =
    SELECTEDVALUE ( Quotes[Start Date] )
VAR DatesGreaterThanCurrentSelection =
    FILTER ( ALL ( Quotes[Start Date] ), Quotes[Start Date] <= CurrentSelection )
VAR Result =
    COUNTROWS ( DatesGreaterThanCurrentSelection )
RETURN
    Result

.

Total Value Rank = 
VAR CurrentQuote = [Total Quote Value]
VAR CurrentDate =
    SELECTEDVALUE ( Quotes[Start Date] )
VAR Temp =
    FILTER (
        ADDCOLUMNS ( ALL ( Quotes ), "Val", [Total Quote Value] ),
        Quotes[Start Date] = CurrentDate
            && [Val] >= CurrentQuote
    )
VAR Result =
    COUNTROWS ( Temp )
RETURN
    Result

.

Created on Rank = 
VAR CurrentQuote = [Total Quote Value]
VAR CurrentDate =
    SELECTEDVALUE ( Quotes[Start Date] )
VAR Temp =
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE ( Quotes, Quotes[Start Date], Quotes[Created On] ),
                "Val", [Total Quote Value]
            ),
            ALL ()
        ),
        Quotes[Start Date] = CurrentDate
            && [Val] >= CurrentQuote
    )
VAR Result =
    COUNTROWS ( Temp )
RETURN
    Result

.

Composite Rank =
[Start Date Rank] * 100 + [Total Value Rank] * 10 + [Created on Rank]
4 Likes