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.

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