Rank one subject based on the other subjects ranking

[tomtang]

Dear all,

I have below table which shows both "Sales" and "Gross Profit" separately.

I've created a measure to rank "Sales" and Gross Profit" as shown in the table.

DAX: 

Rank =
VAR _SumAmountSales = [SumAmount]
RETURN
    RANKX (
        ALLSELECTED ( RankSample[Principal] ),
        CALCULATE ( [SumAmount] ),
        ,
        DESC,
        DENSE
    )

Now I would like to rank the "Gross Profit" based on what has "Sales" been ranked, so I can further filter out Top 3 PRN's "Sales" and "Gross Profit" at the same time

(e.g. PRN5's "Gross Profit" should be ranked as 4 based on its "Sales")

My rough idea is as below, but not sure if it's doable in PowerBI:

1. Rank "Sales" first
2. Based on the "Sales" ranking, create a virtual table and retrieve the PRN name and ranking
3. Look up "Sales" ranking to "Gross Profit" based on the PRN

Sample file is created as below link:

Sample File 

Does anyone have any idea how to do it efficiently?

Thanks in advance!

[v-alq-msft · Accepted solution]

Hi, @tomtang 

You can change the measure as follows.

Rank =

VAR _SumAmountSales =

    CALCULATE (

        [SumAmount],

        ALL ( 'RankSample'[Account] ),

        'RankSample'[Account] = "Sales"

    )

RETURN

    RANKX (

        ALLSELECTED ( RankSample[Principal] ),

        CALCULATE ( [SumAmount], 'RankSample'[Account] = "Sales" ),

        _SumAmountSales,

        DESC,

        DENSE

    )

Result:

If I misunderstand your thought, please show me your expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomtang]

@Ashish_Mathur  & @v-alq-msft 

Thanks for replying to me.

Honestly, when Mathur replied me, I found out the same solution as advised by @v-alq-msft  when I was trying to re-illustrate my expected result more clearly.

However, when I mimic the same DAX back to my official working file, it doesn't work.

So I am trying to figure out what is the difference between my sample and official file.

But still, appreciate for @v-alq-msft  & @Ashish_Mathur 's help.

Happy New Year!

[Ashish_Mathur]

Hi,

Your question is very confusing.  What exactly are you trying to accomplish?  Explain the business context and show the expected output.

[tomtang]

Please help, thank you!