Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Bottom Sorting with same customer name value

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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.