Multi level (sub)ranking

[Wlknsn]

HI,

I’m looking for a simple solution to create a multi level ranking for the model attached. There is a very good demo (https://www.youtube.com/watch?time_continue=291&v=QiWkg0scJHo&feature=emb_title) of this on edna, however, it never uses the subset (only the full table) which unfortunately, I cannot seem to get around.Edna - multilevel ranking.pbix (22.3 KB)

The goal is to rank by:
Start Date (ascending) > Total Quote Value (descending) > Created On (ascending)

This should preferrably result in a new rank value overall. I intended to do this by multiplying the ranks for each and then adding them up.
Example: 100 + 10 + 1 => 111 (is first)

Attached a sample pbix.

[BrianJ]

@Wlknsn,

I’m tied up in meetings all day today, so won’t have time to really dig into this until this evening, but in the interim you may want to take a look at the post below, and also my data challenge entry #4 where I did what I think you are trying to do in developing a composite rank across five dimensions using a subset of the data drawn from slicers via ALLSELECTED.

Ranking members within group

@Mark, Okay, I figured out what was going wrong with your ALLSELECTED construct in Q2. It has to do with how ALLSELECTED behaves when there is no explicit filter context. If instead of using ALLSELECTED as the table modifier in your CROSSJOIN statements when creating your virtual table, use VALUES instead and ALLSELECTED as your CALCULATE/CALCULATETABLE modifier and it all works fine. Here’s an example of one of the modified measures: [image] [image] I hope this is helpful. Full soluti…

i hope this is helpful.

• Brian

[Wlknsn]

No, actually, my scenario is much more simple in a way, yet I believe the goal is slighltly different.
I want the outcome of the first measure to be used, as a group to be ranked by another measure.

Example:
Rank | value | New Rank (descending of Value)
1 | 10 | 1
2 | 15 | 2
2 | 5 | 1
3 | 2 | 1

… and this twice (one level further as well).

[AntrikshSharma]

@Wlknsn Can you show exactly what the result would be in the data that you have provided?

[Wlknsn]

[Wlknsn]

Ps: I don’t mind whether it’s done using measures or calculated columns.

[AntrikshSharma]

@Wlknsn sub rank 2: it should be 1,2,1,1,1,2?

[Wlknsn]

No same start date, same value. In sub rank 3 I split those up by the create date.

[AntrikshSharma]

@Wlknsn Values are different 8880 vs 8800

[AntrikshSharma]

I believe this is what you want: Edna - multilevel ranking.pbix (26.5 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]

[Wlknsn]

Very nice!!!