Forum Discussion

ACraig08's avatar
ACraig08
Icon for Helper III rankHelper III
2 months ago
Solved

Sort Order is messing with measure calculations

Ok, so I have a column that I created in Power Query, Request Notice Bins.

 

Request Notice Bins creates the following values: < 1 Month, 1 - 3 Months, 3 - 6 Months, 6 - 12 Months, 12 - 18 Months, 18 - 24 Months, 24 - 36 Months, 36 - 48 Months, > 4 Years.

 

I created the following table:

I then created the following measures so I could highlight those top two as seen there.

 

Letting Change Request Count = COUNTROWS('Letting Change')

 

Request Notice Total Count =
CALCULATE(
    [Letting Change Request Count],
    FILTER(
        ALL('Letting Change'),
        NOT ISBLANK('Letting Change'[Request Notice Bins]) && 'Letting Change'[Request Type] IN {"Move","Remove"}
    )
)

 

Request Notice % =

DIVIDE(

     [Letting Change Request Count],
     [Request Notice Total Count].

     0

)

 

Request notice % Rank =

RANKX(

     ALL('Letting Change'[Request Notice Bins]),

     CALCULATE([Request Notice %]),

     ,

     DESC,

     Dense

)

 

Request Notice Background Color =

IF(
     [Request notice % Rank] <= 2,

     "#E4F2D3",

     BLANK()
)

 

I applied Request Notice Background Color to the table Cell elements for Notice (in months) column and it worked great, as shown in the image above. 

 

However, when I noticed that the order for Notice (in months) was all messed up I created a Sort Order - Request Notice Bins column in Power Query that would make it so that they would appear in the correct order. It is a whole number data type column. When I went to apply it as the "Sort by column" for Request Notice Bins it broke the Request Notice % Rank measure.

 

Prior to applying the Sort Order it was ranking everything correctly like this:

After applying the sort order it is ranking everything as 1. 

I have tried to figure this out on my own but am totally stuck! I need it to also work when filters on the page are applied. 

 

Any help is greatly appriciated. 

 

 

 

 

Need Help
  • johnt75's avatar
    johnt75
    2 months ago

    You need to remove the sort order column when performing the ranking, as well as the bin name.

    Try

    Request notice % Rank =
RANKX (
    ALL (
        'Letting Change'[Request Notice Bins],
        'Letting Change'[Sort Order - Request Notice Bins]
    ),
    CALCULATE ( [Request Notice %] ),
    ,
    DESC,
    DENSE
)

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    2 months ago

    You need to remove the sort order column when performing the ranking, as well as the bin name.

    Try

    Request notice % Rank =
RANKX (
    ALL (
        'Letting Change'[Request Notice Bins],
        'Letting Change'[Sort Order - Request Notice Bins]
    ),
    CALCULATE ( [Request Notice %] ),
    ,
    DESC,
    DENSE
)
    • ACraig08's avatar
      ACraig08
      Icon for Helper III rankHelper III
      2 months ago

      You are amazing thank you!!! I feel like I spent an entire day trying to look up and figure that out!

  • Parchitect's avatar
    Parchitect
    Icon for Solution Sage rankSolution Sage
    2 months ago
    This is very likely caused by the new Sort by column still being part of the filter context.

     

    Before you added the sort column, removing the filter from Request Notice Bins was enough for RANKX to compare all bins. But after setting Request Notice Bins to sort by Sort Order - Request Notice Bins, Power BI can still keep that sort column in the visual/query context.

     

    So the rank calculation is removing the bin filter, but the current row is still effectively filtered by the sort order value. That is why each row ends up ranking only against itself and returns 1.

     

    I would adjust the ranking logic so it ranks over both columns:

     

    - Request Notice Bins
    - Sort Order - Request Notice Bins

     

    Also, in the total measure, I would avoid using ALL('Letting Change') if you want page filters and slicers to still work. Remove only the bin/sort column filters instead of clearing the whole table.

     

    One more thing to check: each Request Notice Bins value must have one and only one Sort Order value. If the same bin has multiple sort values in the fact table, the sort/rank behavior can become unstable.

     

    Longer term, the cleaner model design would be a small separate bin dimension table with the bin label and sort order, then use that bin label in the visual.

     

    Best regards,
    Solutions Architect · Microsoft Fabric Specialist · Parchitect

    💡Did my response help you? Clicking Kudos is a small gesture that goes a long way, it encourages contributors and helps the community thrive!
    ✔️Did I answer your question? Please mark my post as a Solution, it helps others find the answer faster.