Forum Discussion

Sandeep13's avatar
Sandeep13
Helper III
3 years ago

Rank function Based on ReportedDate like YYYYMM

Hi All,

 

I have 2 columns reporting_mth and Amount, I want to create rank based on reporting_mth and sort data based on amount.

 

Now If you see i have 202203 and 202212 in my data. I am expecting ouput like  

I am expecting output like below .if we have 2 same reportingmonth with different monthnumber  than always give  max Outstanding Amount as rank 1.

 

reporting_mthOutstanding AmountRank
20180329963009505801
20190334310785593351
20200338651453062211
20210340570195156711
20220344067092767762
20221249921657579651

 

ranku PoiwerBInovice po PowerZ PBCommunity 

 

Need Help

10 Replies

    • Sandeep13's avatar
      Sandeep13
      Helper III
      3 years ago

      Hey While using your code i am getting error massage.

      the query referenced calculated column which does not hold any data there is an error in its expression.

       

       

       

       

      • Ahmedx's avatar
        Ahmedx
        Super User
        3 years ago

        Share sample pbix file to help you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Sandeep13 

    You can create a measure

    Measure = var a=LEFT(SELECTEDVALUE('Table'[reporting_mth]),4)
return RANKX(FILTER(ALLSELECTED('Table'),LEFT([reporting_mth],4)=a),CALCULATE(SUM('Table'[Outstanding Amount])),,DESC,Dense)

     

    Best Regards!

    Yolo Zhu

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

    Rank function Based on ReportedDate like YYYYMM.pbix28 KB
    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago

      you create a column, you don't need to create a measure

    • Sandeep13's avatar
      Sandeep13
      Helper III
      3 years ago

      Hi thank you for your response but its give me error.

       

      reporting_mth is a column in my data.

       

       

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago
    Order = 
VAR _t = LEFT( MAX( [reporting_mth],4))
RETURN 
    RANKX(
         FILTER(ALL('Table'), 
          LEFT('Table'[reporting_mth],4)=_t),
            [reporting_mth],,DESC)
    • Sandeep13's avatar
      Sandeep13
      Helper III
      3 years ago

      Still some issue.
      I am Passing dax in measure.

      Getting below errro msg.

      A single value for column 'reporting_mth' in table 'Port_Append' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.