TOP N elements using Visual filter on measure

[Maikeru]

Dear team,

I am trying to create a Top/Bottom N list of sales by customers for which [Sales Total] are between a dynamic range of values chosen by the user.
For this I would like to use the in-built Visual filter on measure in the Filter panel.

To achieve this I have created Rank measure which will dynamically switch from Descending to Ascending ranking order depending on the value of SLICER_RankingOrder (Top or Bottom).

Rank Customer = 
IF (NOT(ISBLANK([Sales Total])),
    SWITCH ( SELECTEDVALUE(SLICER_RankingOrder[SLICER_RankingOrder],"No value"),
        "Top", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,DESC), 
        "Bottom", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,ASC),
        RANKX(ALL(FACT_Sales[Customer]), [Sales Total], ,DESC)),
        BLANK()
)

And from there another measure Sales Top N to blank out any [Sales Total] which are beyond the N rank.

Sales Top N = 
IF ([Rank Customer] <= PARAM_RankingElements[RankingElements Value], [Sales Total], BLANK() )

So far so good.

Now when I apply the Visual filter on Total Sales measure, it seems to be applied on top of the ALLSELECTED customer context.

As a result I will get something like this (red). While the result I am looking for is in blue.

Is there a way to work around this?

 

Note: I found an alternate way using What-if parameters and integrating them in a calculated measure which I will then rank.
The result is fine but in terms of performance when I run this on my production dataset of 1M+ rows, it takes each time 5-10s to run. That's why I am exploring Visual filter on measure which seems to be giving much better performance results.

Thanks in advance for your help.

[v-jiascu-msft · Accepted solution]

Hi @Maikeru,

I thought hard and found the measure doesn't change the context but the values. So I would suggest you add a calculated column to the table 'FACT_Sales'. Then it would be easy now.

TotalSales =
CALCULATE (
    SUM ( FACT_Sales[Sales] ),
    ALLEXCEPT ( FACT_Sales, FACT_Sales[Customer] )
)

Best Regards,

Dale

[Maikeru]

Thank you Dale! It works like a charm!