Top N filter working but not displaying correct result

[Anonymous]

Thanks to the community I now know how to get Top N filtering working. I have created 2 visualisations using the same data - in one case I am showing the Top 3 managers by value and in the other I am showing Bottom 3 managers by value ( I simply copied the visualisation and inverted the filter). Whilst my Top 3 visualisation shows the correct data my bottom 3 does not, it is not displaying the very lowest value.

If I create a list showing all values using the same field and sorting in value order I see my top 3 at the top but the very lowest value in this list does not appear in the visualisation where I am filtering for the bottom 3. What am I doing wrong?

[Ajinkya369 · Accepted solution]

Hi @Anonymous ,

For Top managers use this : 

 Top Managers = RANKX (Employee,[value]) 

Bottom Managers= RANKX (Employee,[value] , , ASC) 

And filter the measure by using Top N filter (For bottom and Top)

If your problem is solved then accept this as a solution.

Thanks

Ajinkya

Analytics Specialist

Master Data Analysis - 

[Anonymous]

I have identified the reason for the problem, there were 2 managers with exactly the same name so when filtering for bottom 3 because their name appeared twice they were excluded from the result as their combined score was not in the bottom 3 - I resolved this by having ManagerA and ManagerB and this fixed the issue with ManagerB being in the bottom 3.

[amitchandak]

@Anonymous , Can you share the formula you have used

[Anonymous]

this is the formula that is used to determine the points scored per week. I am trying to identify the 3 highest and lowest weekly points scores.

Points in week =

CALCULATE(SUM(Points[Points]),FILTER ( ALLSELECTED(  Weeks[Week],Weeks[Week_Value] ), Weeks[Week] = MAX( Weeks[Week] )  ))

    - CALCULATE (

        SUM ( Points[Points] ),

        FILTER ( ALLSELECTED(  Weeks[Week],Weeks[Week_Value] ), Weeks[Week] = MAX( Weeks[Week] ) - 1 )

    )