Forum Discussion

RedJedi_81's avatar
RedJedi_81
Regular Visitor
1 year ago
Solved

Power BI RANKX Issue Summary - Get an average rank for decimal numbers

I am trying to calculate an average rank based on Views_Share_Avg, while considering both Channel and Daypart and excluding the selected date.

However, I am running into issues where:

  1. RANKX does not produce expected ranks (e.g., 1, 2, 3).
  2. Some ranks are duplicated (e.g., two "2"s).

    Rank2 is a column created like this:
    Rank2 =
    VAR CurrentDaypart = MAX('techedge Dayparts for Top Ranked Days'[Daypart])
    VAR CurrentDate = MAX('techedge Dayparts for Top Ranked Days'[Date])
    VAR CurrentChannel = MAX('techedge Dayparts for Top Ranked Days'[Channel])

    RETURN
    RANKX(
        FILTER(
            ALLSELECTED('techedge Dayparts for Top Ranked Days'),
            'techedge Dayparts for Top Ranked Days'[Daypart] = CurrentDaypart &&
            'techedge Dayparts for Top Ranked Days'[Date] = CurrentDate &&
            'techedge Dayparts for Top Ranked Days'[Channel] = CurrentChannel
        ),
        'techedge Dayparts for Top Ranked Days'[Views],
        ,
        DESC,
        DENSE
    )
     
    I have a switchable measure:
     
    Views_Share_Avg =
    VAR AvgValue = AVERAGEX(
        'techedge Dayparts for Top Ranked Days',  
        [Views_Share]
    )
    RETURN
        SWITCH(
            SELECTEDVALUE('Switch Table - Views/Share'[MetricSwitchName]),  -- The slicer selection
            "Views", FORMAT(AvgValue, "#,##0"),  -- Format as thousands
            "Share", FORMAT(AvgValue, "0.00%"),  -- Format as percentage
            BLANK()  -- Default case if nothing is selected
        )
     
     
    an average rank:
     
    Avg_Rank2 = AVERAGE('techedge Dayparts for Top Ranked Days'[Rank2])
     
    and a final rank and reads the average rank:
     
     
    Final_Rank =
    RANKX(
        SUMMARIZE(
            ALLSELECTED('techedge Dayparts for Top Ranked Days'),
            'techedge Dayparts for Top Ranked Days'[Channel],
            'techedge Dayparts for Top Ranked Days'[Daypart],
            'techedge Dayparts for Top Ranked Days'[Date],
            "@AvgRank", [Avg_Rank2]
        ),
        [Avg_Rank2],
        ,
        DESC,
        DENSE
    )

This is the result I get:

1) The ranks are reversed, so the lowest avg_rank2 should be final_rank 1.

 

Expected outcome below:

ChannelDaypartViews_Share_AvgAvg_Rank2Final_Rank
BBC NewsBreakfast44,4572.003
GB NewsBreakfast85,5331.761
Sky NewsBreakfast68,0331.992

 

Here is a sample of the underlying table:

 

DateChannelDaypartRankViewsShareRank2
09/03/2025SKY NEWSPRIME196,5000.85%1
08/03/2025SKY NEWSMORNING1126,8003.23%1
09/03/2025SKY NEWSMORNING1101,4002.34%1
08/03/2025SKY NEWSBREAKFAST1105,0004.04%1
09/03/2025SKY NEWSPAST PRIME168,3002.74%2
09/03/2025SKY NEWSBREAKFAST372,6002.62%2
08/03/2025SKY NEWSPAST PRIME255,8001.92%2
08/03/2025SKY NEWSPRIME2103,8000.93%1
08/03/2025SKY NEWSEARLY PRIME276,1000.76%2
09/03/2025SKY NEWSEARLY PRIME279,3000.70%2
08/03/2025SKY NEWSALL DAY289,1001.41%2
09/03/2025SKY NEWSALL DAY281,5001.24%2
08/03/2025SKY NEWSDAYTIME271,9001.20%2
09/03/2025SKY NEWSDAYTIME273,3001.20%2
08/03/2025BBC NEWSEARLY PRIME1119,9001.19%1
09/03/2025BBC NEWSEARLY PRIME198,6000.88%1
08/03/2025BBC NEWSDAYTIME191,6001.53%2
09/03/2025BBC NEWSDAYTIME190,9001.48%2
08/03/2025BBC NEWSALL DAY193,7001.49%2
09/03/2025BBC NEWSALL DAY182,6001.25%2
08/03/2025BBC NEWSPRIME1125,2001.13%1
08/03/2025BBC NEWSPAST PRIME158,3002.01%2
09/03/2025BBC NEWSPAST PRIME252,6002.11%2
09/03/2025BBC NEWSBREAKFAST282,8002.98%2
09/03/2025BBC NEWSPRIME294,5000.83%2
08/03/2025BBC NEWSMORNING2114,5002.92%1
08/03/2025BBC NEWSBREAKFAST353,1002.04%2
09/03/2025BBC NEWSMORNING366,8001.54%2
09/03/2025GB NEWSBREAKFAST1106,0003.82%1
09/03/2025GB NEWSMORNING286,9002.00%2
08/03/2025GB NEWSBREAKFAST275,1002.89%2
08/03/2025GB NEWSMORNING354,4001.39%2
08/03/2025GB NEWSPRIME341,7000.38%2
09/03/2025GB NEWSPRIME362,6000.55%2
08/03/2025GB NEWSEARLY PRIME349,2000.49%2
09/03/2025GB NEWSEARLY PRIME339,0000.35%2
08/03/2025GB NEWSALL DAY345,0000.71%2
09/03/2025GB NEWSALL DAY361,3000.93%2
08/03/2025GB NEWSDAYTIME334,7000.58%2
09/03/2025GB NEWSDAYTIME353,6000.88%2
08/03/2025GB NEWSPAST PRIME316,0000.55%2
09/03/2025GB NEWSPAST PRIME319,0000.76%2

 

Any help would be most appreciated.

 

Thanks

 

RJ

Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hello RedJedi_81,
    Thank you for reaching out to the Microsoft Fabric Forum Community.

    I’ve reproduced your scenario in Power BI using the sample data, and I achieved the expected output as per your requirements.

    Output:

    For your reference, I’ve attached the .pbix file below so you can review or modify it as needed.

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

7 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    RedJedi_81 Try using

    Ensure that the Rank2 calculation excludes the selected date and ranks the views correctly.

    Rank2 =
    VAR CurrentDaypart = MAX('techedge Dayparts for Top Ranked Days'[Daypart])
    VAR CurrentChannel = MAX('techedge Dayparts for Top Ranked Days'[Channel])
    RETURN
    RANKX(
    FILTER(
    ALL('techedge Dayparts for Top Ranked Days'),
    'techedge Dayparts for Top Ranked Days'[Daypart] = CurrentDaypart &&
    'techedge Dayparts for Top Ranked Days'[Channel] = CurrentChannel
    ),
    'techedge Dayparts for Top Ranked Days'[Views],
    ,
    DESC,
    DENSE
    )

     

    Calculate the average rank for each combination of channel and daypart.

    DAX
    Avg_Rank2 =
    AVERAGEX(
    SUMMARIZE(
    'techedge Dayparts for Top Ranked Days',
    'techedge Dayparts for Top Ranked Days'[Channel],
    'techedge Dayparts for Top Ranked Days'[Daypart],
    "@Rank2", [Rank2]
    ),
    [@Rank2]
    )

     

    Rank the average ranks in ascending order.

    DAX
    Final_Rank =
    RANKX(
    SUMMARIZE(
    ALLSELECTED('techedge Dayparts for Top Ranked Days'),
    'techedge Dayparts for Top Ranked Days'[Channel],
    'techedge Dayparts for Top Ranked Days'[Daypart],
    "@AvgRank", [Avg_Rank2]
    ),
    [@AvgRank],
    ,
    ASC,
    DENSE
    )

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    Not sure I fully understand the average part. Check if this does what you want.

     

    The way RANKX works is that you define a table, the expression is calculated per row and a ranking is performed. In the below we are calculating the number of views, per Day Part and Channel and produces the rank. This means the Channel-Daypart combo with the highest views will be ranked as 1

    Rank =
RANKX(
    ALLSELECTED(
        'techedge Dayparts for Top Ranked Days'[Daypart], 
        'techedge Dayparts for Top Ranked Days'[Channel]
    )
    CALCULATE( 
       SUM( 'techedge Dayparts for Top Ranked Days'[Views] ),
       REMOVEFILTER( 'techedge Dayparts for Top Ranked Days'[Date]
    ),
    ,
    DESC,
    DENSE
)

     

     

  • RedJedi_81's avatar
    RedJedi_81
    Regular Visitor
    1 year ago

    Thank you for the quick reply. 

     

    I forgot to add Rank2 is a column. Does this need to be a measure?

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hello RedJedi_81,
    Thank you for reaching out to the Microsoft Fabric Forum Community.

    I’ve reproduced your scenario in Power BI using the sample data, and I achieved the expected output as per your requirements.

    Output:

    For your reference, I’ve attached the .pbix file below so you can review or modify it as needed.

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

    • v-ssriganesh's avatar
      v-ssriganesh
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi RedJedi_81,
      I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
      Thank you.

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi RedJedi_81,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

     

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi RedJedi_81,
    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank you.