Bottom Sorting with same customer name value

[Anonymous]

I have one matrix visual and i drag the CY_sales and PY_sales dax measure into the Values and customer name in Rows .
Customer    CY_Sales   PY_Slaes
AAAA            20              23
BBBB             null            52
RRRR              63             null
GGGG             0                0
EFGH              0                10
GFDS              0                0
RGHTS            0               63   

now i want to sort bottom wise. here CY_Sales and PY_Sales is Dax measure.
for example, when i select CY and  enter 3(random number i enter) then it will returns bottom 3 values of CY  and py values is as it is.
same senarioes for PY_sales.
expected output : 
Customer    CY_Sales   PY_Slaes
GGGG             0                0
EFGH              0                10
GFDS              0                0

CY_Slaes Dax =

  VAR CY_bottom = IF(SELECTEDVALUE('Top/Bottom Slicer'[Value]) ="CY",

      ROWNUMBER(ALL(Customer[Customer name]),ORDERBY([Current Sales],ASC )),

    // RANKX(

    //         ALLSELECTED(Customer[Customer name]),

    //    [Current sales] ,,

    //     ASC,

    //     DENSE

    // ),

    // RANKX(

    //         ALLSELECTED(Customer[Customer name]),

    //     [Previous sales] ,

    //     ,

    //     ASC,

    //     DENSE

    // )

     ROWNUMBER(ALL(Customer[Customer name]),ORDERBY([Previous sales],ASC BLANKS LAST))

 )

VAR CY_top =IF(SELECTEDVALUE('Top/Bottom Slicer'[Value]) = "CY",

               RANKX(

            ALL(Customer[Customer name]),

         [Current sales], , DESC,Dense),

        RANKX(

            ALL(Customer[Customer name]),

       [Previous sales], , DESC,Dense)

    )

 var  CY_ranking_ = IF(SELECTEDVALUE('Top/Bottom Slicer'[Value]) = "Top", CY_top, CY_bottom)

var result_ =  IF(CY_ranking_ <= selectedvalue(SelectedRange[values]) ,[Current sales])

RETURN result_

similer dax for PY sorting.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try to modify formula provided by amitchandak for better performance like below:

Current Year top 3 = 
CALCULATE(
    SUMX(
        TOPN(3, ALLSELECTED(DimCustomer[CustomerSk]), [Current Year], ASC),
        [Current Year]
    ),
    REMOVEFILTERS(DimCustomer[CustomerSk])
)

Last Year top 3 based on current = 
CALCULATE(
    SUMX(
        TOPN(3, ALLSELECTED(DimCustomer[CustomerSk]), [Current Year], ASC),
        [Last Year]
    ),
    REMOVEFILTERS(DimCustomer[CustomerSk])
)

If you still encounter memory issues, consider reducing the amount of data being processed or optimizing your data model.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , In a visual you can use TOPN Visual level filter.

Or Create two TOPN Measure but both measure should use current Year for TOPn

Current Year top 3 =

Sumx(keepfilters(topn( 3, allselected(DimCustomer[CustomerSk]) ,[Current Year], asc)),[Current Year])

Last Year top 3 based on current =

Sumx(keepfilters(topn( 3, allselected(DimCustomer[CustomerSk]) ,[Current Year], asc)),[Last Year])

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448

[Anonymous]

This solution take too much time to load.....and display the error in powerbi has not engouh memory to perfrom this opration.