Forum Discussion

Kazanskyi's avatar
Kazanskyi
Icon for Helper I rankHelper I
3 years ago
Solved

Remove row context from measure with calculated table

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

 

Need Help
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    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().

8 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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

     

     

    • Kazanskyi's avatar
      Kazanskyi
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        ThxAlot
        Icon for Super User rankSuper User
        3 years ago

        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's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    Regardless you have to calculate it twice. 🙂