Power BI dynamic ranking with some blank values and using slicers

1
votes

I have a table with 15 people that each month get 7-day scores. I want to use the RANKX formula in Power BI to rank the lowest (1) to the highest average score. This works fine if I look at all, but start to act weirdly when I use a slicer and only look at one or two months for example. The ranking doesn't start with 1 anymore?

I use this formula:

Rank = RANKX(
    ALLSELECTED('Score Table'[Person]);CALCULATE(AVERAGE('Score Table'[Score]));;ASC;Dense)

Look at the image attached, please.

Help much appreciated image showing the issue

1
powerbiranking

1 Answers

0
votes

Can you try this and see if it works?

Rank =
RANKX(
    CALCULATETABLE(
        VALUES( 'Score Table'[Person] ),
        ALLSELECTED( 'Score Table'[Person] )
    ),
    CALCULATE( AVERAGE( 'Score Table'[Score] ) ),
    ,
    ASC,
    Dense
)

Let's think about the original code step by step.

  1. It iterates over "Person 1" to "Person 20" and calculates the average score of that person.
  2. Evaluate the average score of the person of current filter context (say "Person 1").
  3. Find the ranking position of "Person 1" in 20 persons.

In the step (1), it includes all Persons from 1 to 20 because there is no Person filter in the visual. Here, it looks there is no scores of Person 15 and 18 in the selected period, so it evaluates to BLANK.

Now, the document of RANKX says,

If expression or value evaluates to BLANK it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.

The average scores of Person 8 and 15 are BLANK, so RANKX treats it as 0. Now going back to Person 1, her average score was 62.43, and there were two people with average score of 0. Therefore, the rank of Person 1 will be 2.

By wrapping 'Score Table'[Person] with VALUES inside CALCULATETABLE, you can omit persons who has no scores in the selected period.