Dynamic Ranking - Contract Type, Contract Name, and Total Amount - Rankx

[jmandhare]

Hello All,

I am currently facing an issue with creating a dynamic rank. I am still at the beginning phase of DAX so not able to crack how rankx works. 
Problem:

I have two tables view_contracts and view_payments

View_contracts  has the following columns in it
contracts_id   contract_name   contract_type

1201                     ABC                   On_call

2311                     DEF                    On-call
5402                     GHI                    MD

7402                     JKL                    PS

View_Payments has the following columns in it:
contracts_id   Total Amount     Approved_at_date    Status

1201                     400                   01.01.2022           Approved

1201                     200                   02.01.2022           Decline
1201                     100                   03.02.2022           Approved

2311                     4000                 01.01.2022           Approved

2311                     8000                 05.01.2022           Approved

5402                     9000                 01.01.2022           Approved
7402                     2000                 01.01.2022           Approved

The result I want to achieve:

I want to rank sum of view_payments[total amount] of each view_payments[Contract_id] by View_Contracts[Contract_type] and it should be dynamic- so if I change the view_payments[Approved_at_date] filter it should change the visual accordingly and as per those dates assigning new ranks and showcase View_Contracts[contract_names] and view_payments[total amount] approved in those days.
Here is the result I want to showcase if i consider filter view_payments[approved_at_date] between 01.01.2022 to 02.01.2022  && view_payments[status] = 'Approved':

Contract Type       Contract Name  Total Amount   Rank
On-Call                   ABC                     500                  2
On-Call                   DEF                      4000                1

MD                         GHI                      9000                 1

PS                            JKL                      2000                 1

Note: Approved_at_date and status column filter from the view_payments table will vary as per user needs. Based on those filters the RANK values should change under each contract_type as per the total amount.

Looking forward to your response.

[jmandhare · Accepted solution]

I guess no one able to find the solution for this yet. I was able to crack it myself. Just posting it over here if anyone wants to know the solution for it.

For this, you need to create two measures in the view_contracts table:

ISCategory (Require for Rank by Contract Type) = COUNTROWS('view_contracts') = CALCULATE(COUNTROWS('view_contracts'),ALL('view_contracts'), VALUES('view_contracts'[contract_type]))

Rankx by contract_type = IF( [ISCategory (Require for Rank by Contract Type)], RANKX( ALLSELECTED(view_contracts[contract_type]),CALCULATE(SUM(view_payments[total_amount]))), RANKX(ALLSELECTED(view_contracts[contract_name]),CALCULATE(SUM(view_payments[total_amount])),,DESC,Skip))

I have to redact some information. I hope it helps someone who is looking for it.

Regards,

JM