Rank

[JamesBurke]

Hi All , 

I have a Ranking system : 

Rank = 
VAR _RANK =
RANKX(FILTER(ALL('Emporia Devices'[Wawrick Device Name]) , 'Emporia Devices'[Wawrick Device Name] <> BLANK()), [Total solar Generated],,DESC,Dense)
RETURN
IF(
    MAX('Emporia Devices'[Wawrick Device Name]) <> BLANK(),
    _RANK,
    BLANK()
)

However when i have stores that are the same Rank for example : 

Store Name	Rank	Usages
Store 1	1	15
Store 2	2	10
Store 3	2	10

I would like it to Rank based of Store name is Alphabetical order as decider so it would be : 

Store Name	Rank	Usages
Store 1	1	15
Store 2	2	10
Store 3	3	10

Any Help would be appericated , 

Thanks , James. 

[Thennarasu_R]

Hi @JamesBurke 

Good Day !

For your requirement you can use rankx function with caluculated column . below I have mention the Formulas for your requirements

Thanks ,

Thennarasu R

[SachinNandanwar]

You can also use ROWNUMBER which is one of my faviorties to handle these kind of conditions

CustomRank = 
ROWNUMBER(FILTER(ALL('Emporia Devices') , 'Emporia Devices'[Wawrick Device Name] <> BLANK()),ORDERBY('Emporia Devices'[Store Name],ASC))

[v-jianpeng-msft · Accepted solution]

Thank you OwenAuger and SamWiseOwl 

Hi, @JamesBurke 

Based on your descriptive information, as well as the example data provided and the expected output, I created the following example data:

I created a measure using this DAX expression below:

Rank = 
VAR _rank =
    RANK (
        FILTER (
            ALLSELECTED ( 'Emporia Devices' ),
            'Emporia Devices'[Wawrick Device Name] <> BLANK ()
        ),
        ORDERBY (
            CALCULATE ( SUM ( 'Emporia Devices'[Total Solar Generated] ) ), DESC,
            CALCULATE ( MAX ( 'Emporia Devices'[Store Name] ) ), ASC
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Emporia Devices'[Wawrick Device Name] ) <> BLANK (),
        _rank,
        BLANK ()
    )

The results are as follows:

I have provided the PBIX file used in this instance below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JamesBurke]

HI @SachinNandanwar , @v-jianpeng-msft  , @SamWiseOwl  , @OwenAuger  

Thanks for the quick responses , for some reason on my end these Return Blank ? 

I have tried all of them and they come back blank , guessing it's something on my end ? 

Not sure . 

Thanks , James

[v-jianpeng-msft]

Hi, @JamesBurke 

Thank you for your reply. Can you adjust the DAX expression provided above to suit your data set requirements? In your report, there may be different contexts that affect the rank results.
In the DAX expression I provided, the primary and secondary sorts are mainly implemented through ORDERBY.
The first parameter is the primary sorting basis: 'Emporia Devices'[Total Solar Generated]
The third parameter implements the secondary sorting (based on the sorting of one parameter): 'Emporia Devices'[Store Name]
In this way, Store can be considered when sorting Usages.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JamesBurke]

HI @v-jianpeng-msft , 

Was a case of changing all selected from the entire Table to the specfic column so it was all selected. 

Thanks for your response. 

James. 

[v-jianpeng-msft]

Hi, @JamesBurke 

Have you solved the current problem? If yes, you can mark the helpful reply as a solution so that others in the community can find the answer quickly. If no, can you continue to describe your dataset in detail or provide some sample PBIX files that are closest to your situation and do not contain privacy?

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl]

Hi @JamesBurke 

Modified Rank =

var _sales = [Total solar Generated]

VAR _RANK =

RANKX(FILTER(ALL('Emporia Devices'[Wawrick Device Name]) , 'Emporia Devices'[Wawrick Device Name] <> BLANK()), [Total solar Generated],,DESC,Skip)

--var _test = RANKX(all('Emporia Devices'),[Wawrick Device Name],,DESC)

var matches = RANK(DENSE,FILTER(ALL('Emporia Devices'), [Total solar Generated] = _sales),ORDERBY([Wawrick Device Name]))

RETURN

IF(

    HASONEVALUE('Emporia Devices'[Wawrick Device Name]) ,

  _RANK+ ( matches-1),

    BLANK()

)

First calculate and store the current sales.

Then do the rank as normal using SKIP to create gaps.

Rank again but only items that have the same sales as the current row.

Add this secondary rank onto the first rank less 1.

[OwenAuger]

Hi @JamesBurke 

The RANK function is useful when you need to break ties like this.

Here is a suggested version of your measure using RANK and rewritten slightly:

Rank = 
VAR SourceTable =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Emporia Devices'[Wawrick Device Name] ),
            NOT ISBLANK ( 'Emporia Devices'[Wawrick Device Name] )
        ),
        "@TotalSolarGenerated", [Total solar Generated]
    )
VAR _Rank =
    RANK (
        DENSE,
        SourceTable,
        ORDERBY (
            [@TotalSolarGenerated], DESC,
            'Emporia Devices'[Wawrick Device Name], ASC
        )
    )
RETURN
    IF (
        NOT ISBLANK ( MAX ( 'Emporia Devices'[Wawrick Device Name] ) ),
        _Rank
    )

Does this work at your end?

[SamWiseOwl]

@OwenAuger you beat me to it and with much neater code, nice one!

lol 24 seconds: