Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomtang
Helper III
Helper III

Rank one subject based on the other subjects ranking

Dear all,

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

Capture1.PNG

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!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

c1.png

 

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.

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

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:

c1.png

 

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.

@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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tomtang
Helper III
Helper III

Please help, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.