Trying to create a measure that return top 5 of another measure, but all values are returned

[Anonymous]

Hi All,

I hope someone can help me solve my issue. 

I'm trying to find the top 5 values of a variable stored inside a measure using the TOPN function - however all values are returned. 

The variable consists of a if-statement and the ALLSELECTED function (not sure if this messes up the TOPN-func?)

Here's my measure that I've built:

Measure = 

VAR rank1 = 

IF (
        NOT ( ISBLANK ( 'Salesforce'[Brutto sales] ) ) && Salesforce[Total sales]>=85000 && NOT ( 'Salesforce'[Total sales]=0 ) && [no ready]>=30,
        
        RANKX (
            FILTER ( ALLSELECTED ( Agent[Name]), NOT ( ISBLANK ( 'Salesforce'[Brutto sales] ) ) ),
            'Salesforce'[Brutto sales],,DESC
        )
    
+


        RANKX (
            FILTER ( ALLSELECTED ( Agent[Name] ), NOT ( ISBLANK ( 'Salesforce'[Total sales] ) ) ),
            'Salesforce'[Total sales],,DESC
        )+[RANDOM]
    
,



        RANKX (
            FILTER ( ALLSELECTED ( Agent[Name] ), NOT ( ISBLANK ( 'Salesforce'[Brutto sales] ) ) ),
            'Salesforce'[Brutto sales],,DESC
        )
    
+

        RANKX (
            FILTER ( ALLSELECTED ( Agent[Name] ), NOT ( ISBLANK ( 'Salesforce'[Total sales] ) ) ),
            'Salesforce'[Total sales],,DESC
        )+1000+[RANDOM]
)

return


CALCULATE(rank1,KEEPFILTERS(
TOPN(5,ALL(Agent[Name]),rank1,DESC)))

As you see on the picture below - my measure returns all values instead of only the top 5:

Can someone help me solve this issue, so all other values (instead of the top 5) i blank?

Best,

NicoM96

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=inJ3Yt7ay30

[Greg_Deckler]

@Anonymous Try using Top N filtering in Filter pane for Agent based on Measure?

[Anonymous]

Hi Greg,

Thanks for a quick response on my question.

However the TOPN function in the filter pane is not a suitable solution for my issue as I want to manipulate the measure further to return middle values e.g. top 6-10 etc.

Do you know how I can correct the measure so I don't have to use the filter pane?