Advanced RANKX help needed

[Anonymous]

Hi again,

I have another thread about this - but was unable to get the help I needed.  I think this is pretty complicated:

Here is a table visual in Power BI Desktop with dummy data I've created:

I first need to sort this table by the 'Total Plan Paid' amount in descending order.  'Total Plan Paid' is a measure I created:

Total Plan Paid = CALCULATE ( SUM ( 'SQL Shock Claims Query'[Total Plan Paid] ) )

I also need to have the table sorted by the 'Charges' in descending order.

In other words I need the table to eventually look like this:

As you can see, MemberID F2 is on top because the member's [Total Plan Paid] is the most.  F2's 'Charges' are also sorted properly because the hiearchy of sorting is [Total Plan Paid] first and then followed by 'Charges'.

Member C9 is second because their [Total Plan Paid] is second most.  Their 'Charges' are also sorted properly in descending order.

Member A6 is at the bottom because their [Total Plan Paid] is the least.  Their 'Charges' are also sorted properly in descending order.

The other part of the problem is that the field 'MemberID' is classified and I don't want to show that in the table.  So I was hoping to have some kind of field/rank where I could switch it out in place of MemberID.

So, in the very end I would hope the table would look like this:

Any help would be great.  I've been trying to figure this out for the past day without any luck!

Andrew

[Anonymous · Accepted solution]

Figured it out:

This is what I needed/came up with:

RankOrder =

VAR MemberID =

CALCULATETABLE (

VALUES ( 'SQL Shock Claims Query'[MemberID] ),

ALLSELECTED ( )

)

VAR CurrentOrder =

SELECTEDVALUE ( 'SQL Shock Claims Query'[MemberID] )

VAR Result =

IF (

HASONEVALUE ( 'SQL Shock Claims Query'[MemberID] ),

RANKX (

MemberID,

'SQL Shock Claims Query'[MemberID],

CurrentOrder,

DESC

)

)

RETURN

Result

[v-easonf-msft]

Hi, @Anonymous 

Great to hear the problem got resolved! Please accept your reply above as solution to close this thread.

Other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason