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

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
    Icon for Community Support rankCommunity 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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie and thank you,

       

      Not sure if I did something wrong or this is due to data formats i have.

       

      This is result i got (i have also changed format to match what i have):

       

        Format is 1st day of monthMeasure 2Measure 1
      NameGroupMonthRankSum%
      John Smith (IDC123456)Name_of_the_group oneOctober 191202
      John Smith (IDC123456)Name_of_the_group oneNovember 1918.85
      John Smith (IDC123456)Name_of_the_group twoNovember 19138.22

       

      This is rank functon used:

       

      rank = RANKX (
      FILTER (
      ALLSELECTED ('table name'),
      'table name'[Month] = MAX('table name'[Month])
      && 'table name'[Name]= MAX ('table name'[Name])
      ),
      [sum%],
      ,
      DESC,
      DENSE
      )
       
      What did i do wrong?
      Regards
      Filarap
      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

        "Format is 1st day of month",

        How do you format the month? 

        Use Format function or anything else?

         

        How do the [sum%] calculate?

         

        I can't reproduce your problem,

        Could you share a sample data for me to test?

         

        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.