Remove row context from measure with calculated table

[Kazanskyi]

I want to create a measure to show values only for TOP 5 and Bottom 5 performers together in one table.

I know how to do this with TOPN, but I need to repeat it with RANKX and compare performance as the amount of data is big.

The calculated table in the formula works as expected when I check with a physical table, but I can't make it work when I'm trying to use calculation in a measure.

Here is the formula:

Top and Bottom =

VAR _table = FILTER(

                        VALUES(DIM_Product[Product CD]),

                        DIM_Product[Product CD] <> BLANK())

VAR _ranked_table =

                ADDCOLUMNS(

                        _table

                        ,"_rank",RANKX(_table,[Total Sales],,DESC,Skip))

VAR max_rank = MAXX(_ranked_table,[_rank])

VAR _top = filter(_ranked_table,[_rank]<=5)

VAR _bottom = filter(_ranked_table,[_rank]>max_rank-5)

RETURN

CALCULATE (

        [Total Sales],

        UNION(_top,_bottom),

        ALLSELECTED(DIM_Product[Product CD])

)

Here is a link to the sample dataset 
link

[Kazanskyi]

Thank you for your answers @parry2k  and @ThxAlot !

[parry2k]

@ThxAlot I liked this one. I will use this one, much cleaner. 

[parry2k]

Regardless you have to calculate it twice. 🙂

[parry2k]

@Kazanskyi I think you shouldn't worry about if it is getting calculated twice or not, check the performance using the performance analyzer and then decide.

[parry2k]

@Kazanskyi and here is the output:

[Kazanskyi]

Thank you for your idea, @parry2k 

I did something similar with TOPN, but the problem with these solutions is that the table is recalculated 2 times to obtain the output.

With RANKX it should be only 1 iteration that in theory should be faster. 

If you have an approach how to do this with one iteration, that would be amazing!

[ThxAlot · Accepted solution]

If you insist,

Top and Bottom VirtualTable = 
VAR __t = ADDCOLUMNS( ALLSELECTED( DIM_Product[Product CD] ), "@sales", [Sum Sales] )
RETURN
    CALCULATE(
        [Sum Sales],
        KEEPFILTERS(
            UNION( TOPN( 5, __t, [@sales] ), TOPN( 5, __t, [@sales], ASC ) )
        )
    )

But I didn't see any advantage over TOPN() or WINDOW().

[parry2k]

@Kazanskyi I love new window functions, and tried that to solve it:

Measure = 
CALCULATE (
        [Total Sales],
        KEEPFILTERS (
                UNION (
                        WINDOW ( 
                                1, ABS,
                                5, ABS,
                                ALLSELECTED ( DIM_Product ),
                                ORDERBY ( [Total Sales], DESC )
                        ),
                        WINDOW ( 
                                1, ABS,
                                5, ABS,
                                ALLSELECTED ( DIM_Product ),
                                ORDERBY ( [Total Sales], ASC )
                        )
                )
        )
)

You can apply further filters if you want, like checking the BLANK product etc.

If you are interested in learning these new functions, check the playlist on my YT channel: https://www.youtube.com/playlist?list=PLiYSIjh4cEx0BDzmo48YIPzw_dIC0Kd95