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