Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rank using multiple attribute columns and 1 value column

Hi all,

 

Maybe i am stupid, but i am strugling with Ranking formula quite a bit and was not able to find solution afer looking into various online rescources.

 

I have dataset with various people, working in different groups during a month. I need to rank their time per group each month (1,2,3) and then use filters to use , ex. only number 1 ranks.

 

I have tried various examples of calculate, rankx, allselected, allexcept, but nothing gave me what i needed. Please help!

 

Dataset looks something like this :

 

NameGroupMonthValue
N1G1M1100
N1G1M1100
N1G1M2150
N1G2M150
N2G1M1100
N2G1M1100
N2G1M2150
N2G2M150
N3G1M1100
N3G1M2150
N3G2M150
N4G1M1100
N4G1M2150
N4G2M150

 

Output would like something like this:

 

NameGroupMonthRank
N1G1M11
N1G1M21
N2G1M11
N2G1M21
N3G1M11
N3G1M21
N4G1M11
N4G1M21

 

Filarap

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a calcualted column,

    month-year = FORMAT([Day],"Mmmm YYYY")

    Then create two measures

    sum =
CALCULATE (
    SUM ( 'Table 3'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[Name] = MAX ( 'Table 3'[Name] )
            && 'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Group] = MAX ( 'Table 3'[Group] )
    )
)

rank =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Name] = MAX ( 'Table 3'[Name] )
    ),
    [sum],
    ,
    DESC,
    DENSE
)

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous Please create a calculated column 

    Column = 
VAR _month ='Table'[Month]
RETURN RANKX(FILTER('Table','Table'[Month]=_month),'Table'[Value],,,Dense)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you vimal_parmar,

       

      I now have ranking based on a month, but i am missing it on name and group level. How can i add other dimensions?

       

         Rank
      NameGroupMonthWhat I have nowWhat I need
      N1G1M12021
      N1G2M12132
      N1G1M22151

       

      Regards

      Filarap

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        Create two measures

        Measure = SUM('Table 2'[Value])

Measure 2 =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 2' ),
        'Table 2'[Month] = MAX ( 'Table 2'[Month] )
            && 'Table 2'[Name] = MAX ( 'Table 2'[Name] )
    ),
    [Measure],
    ,
    DESC,
    DENSE
)

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Your expected result in the first post is confusing.  Why should there be a rank 1 for all rows?  Please explain.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ashish_Mathur 

       

      In final output, i considered that i have already filtered a table to show only rank 1.

      Goal is to see only highest value group per person per month.

       

      Hope it makes it clearer

      Filarap

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        Based on the first Table that you have shared in your first post, please show the exact rank that you are expecting.