Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 _rank

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian

     

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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