Rank results of measure

[Meep]

Hi all, 

I am struggling to make this work:

Order IDContactReceived DateWin DateStatus

1	Rob	01 January 2025	01 February 2025	Won
2	Bob	02 January 2025		Quoted
3	Rob	05 January 2025		Quoted
4	Bob	10 December 2024	15 January 2025	Won
5	Bob	15 January 2025	20 January 2025	Won

This is a mock table and I am trying to return a rank of the contacts for their respective win rates.

Based on this (https://community.fabric.microsoft.com/t5/Desktop/Ranking-a-measure/m-p/539066#M253102) threat, I almost did it, but it seems that the rank (_tmp) variable does not work as intended. 

Rank_attempt =

var _quoted =  COUNTX(

FILTER(

Orders,Orders[Received Date].[Date]>=Date(2025,01,01)

&&Orders[Received Date].[Date]<=Date(2025,12,31)

&&Orders[Status]<>"Declined"),Orders[Order ID])

var _won =  COUNTX(

FILTER(

Orders,Orders[Win Date].[Date]>=Date(2025,01,01)

&&Orders[Win Date].[Date]<=Date(2025,12,31)

&&Orders[Status]="Won"),Orders[Order ID])

var _win_rate =  _won/_quoted

var _summry = SUMMARIZE(ALLSELECTED(Orders),[Contact],"Hit",_win_rate)

var _tmp = ADDCOLUMNS(_summry,"RNK",RANKX(_summry,[Hit],,DESC,Dense))

return MAXX(FILTER(_tmp,[Contact] = SELECTEDVALUE(Orders[Contact])),[RNK])

I am not sure what the problem is, because if I change the return synthax with return MAXX(FILTER(_tmp,[Contact] = SELECTEDVALUE(Orders[Contact])),[Hit]), the values come up as correct.

MAXX returning value of [Hit] :

MAXX returning the value of [RNK]:

Is there a solution to this? 

Thank you.

[pankajnamekar25]

@Meep 

please try this

Rank_attempt =

VAR _summry =

    ADDCOLUMNS(

        SUMMARIZE(ALLSELECTED(Orders), Orders[Contact]),

        "Hit",

            CALCULATE(

                DIVIDE(

                    COUNTROWS(

                        FILTER(

                            Orders,

                            Orders[Win Date].[Date] >= DATE(2025,1,1)

                                && Orders[Win Date].[Date] <= DATE(2025,12,31)

                                && Orders[Status] = "Won"

                        )

                    ),

                    COUNTROWS(

                        FILTER(

                            Orders,

                            Orders[Received Date].[Date] >= DATE(2025,1,1)

                                && Orders[Received Date].[Date] <= DATE(2025,12,31)

                                && Orders[Status] <> "Declined"

                        )

                    )

                )

            )

    )

VAR _tmp =

    ADDCOLUMNS(

        _summry,

        "RNK", RANKX(_summry, [Hit], , DESC, DENSE)

    )

RETURN

    MAXX(

        FILTER(_tmp, Orders[Contact] = SELECTEDVALUE(Orders[Contact])),

        [RNK]

    ) 

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[v-ssriganesh · Accepted solution]

Hi @Meep,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced the scenario you described using the sample data you provided and was able to achieve the expected output. Below, I’ll Walk you through the steps I took, share the corrected DAX measures, and provide the output screenshot for your reference. I’ve also attached a .pbix file for you to review the solution in Power BI.

In your original Rank_attempt measure, the win rate (_win_rate) was calculated at the table level, not per contact. This caused RANKX to see the same win rate for all contacts, resulting in both Bob and Rob being ranked 1. The corrected DAX calculates the quoted and won counts per contact within the SUMMARIZE function, ensuring that RANKX ranks the contacts based on their individual win rates.

Here are the two measures I used to achieve the correct output:

• This measure calculates the win rate for each contact, which helps verify the intermediate values and display them in the final output.

Win Rate =

VAR _summry =

SUMMARIZE(

    ALLSELECTED(Orders),

    Orders[Contact],

    "Quoted",

        CALCULATE(

            COUNTROWS(Orders),

            Orders[Received Date] >= DATE(2025, 1, 1),

            Orders[Received Date] <= DATE(2025, 12, 31),

            Orders[Status] <> "Declined"

        ),

    "Won",

        CALCULATE(

            COUNTROWS(Orders),

            Orders[Win Date] >= DATE(2025, 1, 1),

            Orders[Win Date] <= DATE(2025, 12, 31),

            Orders[Status] = "Won"

        )

)

VAR _tmp =

ADDCOLUMNS(

    _summry,

    "WinRate", DIVIDE([Won], [Quoted], 0)

)

RETURN

MAXX(

    FILTER(

        _tmp,

        [Contact] = SELECTEDVALUE(Orders[Contact])

    ),

    [WinRate]

)

• This measure ranks the contacts based on their win rates in descending order.

Rank_attempt =

VAR _summry =

SUMMARIZE(

    ALLSELECTED(Orders),

    Orders[Contact],

    "Quoted",

        CALCULATE(

            COUNTROWS(Orders),

            Orders[Received Date] >= DATE(2025, 1, 1),

            Orders[Received Date] <= DATE(2025, 12, 31),

            Orders[Status] <> "Declined"

        ),

    "Won",

        CALCULATE(

            COUNTROWS(Orders),

            Orders[Win Date] >= DATE(2025, 1, 1),

            Orders[Win Date] <= DATE(2025, 12, 31),

            Orders[Status] = "Won"

        )

)

VAR _tmp =

ADDCOLUMNS(

    _summry,

    "WinRate", DIVIDE([Won], [Quoted], 0),

    "Rank", RANKX(_summry, DIVIDE([Won], [Quoted], 0), , DESC, Dense)

)

RETURN

MAXX(

    FILTER(

        _tmp,

        [Contact] = SELECTEDVALUE(Orders[Contact])

    ),

    [Rank]

)

Expected Output screenshot:

Thank you, @johnbasha33 for sharing your valuable insights.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Meep]

Hi v-ssriganesh,

Thank you for the help! It worked on my original data as well which is great. Is it okay if I make another post and mention you so you could have a look at another issue that is currently arising? The measure behaves weirdly in visuals when I introduce relationships. 

Thank you. 

Preview

[johnbasha33]

Hi,

The issue comes from how _win_rate is calculated inside SUMMARIZE. Your approach calculates _win_rate as a scalar variable outside of SUMMARIZE, meaning it remains constant rather than evaluating per Contact.

Issue Breakdown

1. _win_rate is calculated once and used in SUMMARIZE, which prevents it from being recalculated per contact.
2. RANKX is ranking a single value instead of dynamic values for each Contact.

   ---

   Solution Approach

   • Instead of defining _win_rate before SUMMARIZE, calculate it inside using DIVIDE().
   • Use SUMMARIZE properly to generate a distinct list of contacts with their win rates.
   • Apply RANKX dynamically.

     ---

     Updated Measure

     Rank_attempt =
     VAR _summry =
         ADDCOLUMNS(
             SUMMARIZE(Orders, Orders[Contact]),
             "Quoted",
                 CALCULATE(
                     COUNT(Orders[Order ID]),
                     Orders[Received Date] >= DATE(2025, 01, 01),
                     Orders[Received Date] <= DATE(2025, 12, 31),
                     Orders[Status] <> "Declined"
                 ),
             "Won",
                 CALCULATE(
                     COUNT(Orders[Order ID]),
                     Orders[Win Date] >= DATE(2025, 01, 01),
                     Orders[Win Date] <= DATE(2025, 12, 31),
                     Orders[Status] = "Won"
                 ),
             "Hit",
                 DIVIDE([Won], [Quoted], 0)  -- Avoid division by zero
         )
     
     VAR _tmp = ADDCOLUMNS(_summry, "RNK", RANKX(_summry, [Hit], , DESC, DENSE))
     
     RETURN 
     MAXX(FILTER(_tmp, Orders[Contact] = SELECTEDVALUE(Orders[Contact])), [RNK])

     ---

     Why This Works

     ✅ SUMMARIZE(Orders, Orders[Contact]) ensures we get distinct contacts.
✅ ADDCOLUMNS dynamically calculates Quoted, Won, and Hit per contact.
✅ DIVIDE([Won], [Quoted], 0) ensures no errors from division by zero.
✅ RANKX now operates on correctly calculated win rates per contact.

     ---

     Expected Output

     For your sample data, the ranking should be:

     1. Rob → 50% win rate → Rank 2
     2. Bob → 66.7% win rate → Rank 1

        Now, your ranking should work as intended in Power BI! 🚀 Let me know if you need tweaks.
        
        

        Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Meep]

Hi, 

Thank you for the help, but it seems that I need an extra var step after _summry becuse the model does not recognise the columns "Quoted" and "Won" to calculate "Hit" in the same variable. Adding it after with another var did the trick. At least that happened for me. 

Thanks.