# Multi level (sub)ranking

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.

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.

• Brian

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

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

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

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

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

@Wlknsn Values are different 8880 vs 8800

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 (
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

Very nice!!!