Forum Discussion
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_mth | Outstanding Amount | Rank |
| 201803 | 2996300950580 | 1 |
| 201903 | 3431078559335 | 1 |
| 202003 | 3865145306221 | 1 |
| 202103 | 4057019515671 | 1 |
| 202203 | 4406709276776 | 2 |
| 202212 | 4992165757965 | 1 |
ranku PoiwerBInovice po PowerZ PBCommunity
10 Replies
- AhmedxSuper User
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RiHACZ_Smu67Qkufg?e=tdVPVU - AnonymousNot applicable
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.
- AhmedxSuper User
pls try this
- AhmedxSuper User
Order = VAR _t = LEFT( MAX( [reporting_mth],4)) RETURN RANKX( FILTER(ALL('Table'), LEFT('Table'[reporting_mth],4)=_t), [reporting_mth],,DESC)- Sandeep13Helper III
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.