Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago
Solved

Rank

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 NameRankUsages
Store 1 115
Store 2210
Store 3210

 

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

 

Store NameRankUsages
Store 1 115
Store 2210
Store 3310

 

Any Help would be appericated , 

 

Thanks , James. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

10 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    2 years ago

    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's avatar
    OwenAuger
    Super User
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JamesBurke
      Helper III
      2 years ago

      HI SachinNandanwar , Anonymous  , 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • SachinNandanwar's avatar
    SachinNandanwar
    Impactful Individual
    2 years ago

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




  • Thennarasu_R's avatar
    Thennarasu_R
    Responsive Resident
    2 years ago

    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