Forum Discussion
Need Help on create the Measure
I am trying to create the Measure where the below table formation . Please help me how to create the Measure where it should count the recent transaction date based on ID as higher rank. Each Rank I want to calculate the total net amount , Paid Amount , Rejected Amount. I attempted countrows with filter option & RankX but it showing error .
| Transaction Date | ID | Service Code | Net Amount | Paid Amount | Rejected Amount | Manual mapping |
| 7/7/2024 | CLA0001 | 57250 | 250 | 100 | 150 | 1st Received transaction |
| 8/8/2024 | CLA0001 | 57250 | 150 | 150 | 0 | 2nd Recived Transaction |
| 6/6/2024 | CLA0005 | 88305 | 200 | 200 | 0 | 1st Received transaction |
| 6/6/2024 | CLA0005 | 99213 | 285 | 0 | 285 | 1st Received transaction |
| 6/6/2024 | CLA0005 | 71046 | 110 | 0 | 110 | 1st Received transaction |
| 7/7/2024 | CLA0005 | 99213 | 285 | 285 | 0 | 2nd time received |
| 7/7/2024 | CLA0005 | 71046 | 110 | 0 | 110 | 2nd time received |
| 8/7/2024 | CLA0005 | 71046 | 110 | 110 | 0 | 3rd time Received |
Hello,rajendraongole1 ,thanks for your concern about this issue.
Your answer is excellent! And I would like to share some additional solutions below.
Hi,Anonymous .I am glad to help you.
If the calculated columns don't fit your needs, here is what I calculated by creating a measure, which enables dynamic transformations based on the data in the visual.
In fact I only modified the code rajendraongole1 provided by using RANK().
measure:M_Rank = VAR _rank= RANK(DENSE,FILTER(ALL(Rknk),'Rknk'[ID]=MAX('Rknk'[ID])),ORDERBY('Rknk'[Transaction Date],DESC),DEFAULT) RETURN _rankI hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
4 Replies
Hi Anonymous - create a new calculated column to calcuate the rank the transactions for each id.
Rank:
TransactionRank =RANKX(FILTER('Rknk','Rknk'[ID] = EARLIER('Rknk'[ID])),'Rknk'[Transaction Date],,DESC,DENSE)create measures to calculate the total Net Amount, Paid Amount, and Rejected Amount
TotalNetAmount =CALCULATE(SUM('Rknk'[Net Amount]),FILTER('Rknk','Rknk'[TransactionRank] = 1))total paid amount measure:TotalPaidAmount =CALCULATE(SUM('Rknk'[Paid Amount]),FILTER('Rknk','Rknk'[TransactionRank] = 1))total rejected amount measure :TotalRejectedAmount =CALCULATE(SUM('Rknk'[Rejected Amount]),FILTER('Rknk','Rknk'[TransactionRank] = 1))Hope it helps, replace with your table name as per your model.
rajendraongole1 Thanks for your reply. That means rank always should be calculated column . I have done same earlier in microsoft excel data model . Unfortunately I couldnt do as rank Measure for better performance. correct me if I am wrong, so Everytime I should create calculated column in the Model where the data range is changing( adding new data weekly once).
Hello,rajendraongole1 ,thanks for your concern about this issue.
Your answer is excellent! And I would like to share some additional solutions below.
Hi,Anonymous .I am glad to help you.
If the calculated columns don't fit your needs, here is what I calculated by creating a measure, which enables dynamic transformations based on the data in the visual.
In fact I only modified the code rajendraongole1 provided by using RANK().
measure:M_Rank = VAR _rank= RANK(DENSE,FILTER(ALL(Rknk),'Rknk'[ID]=MAX('Rknk'[ID])),ORDERBY('Rknk'[Transaction Date],DESC),DEFAULT) RETURN _rankI hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson JianAcutally as per rajendraongole1 I have done the calculated column and the measure as well added . it was worked well. Anonymous Thanks for your update regarding the measure. It's working well also.
