Forum Discussion

Akhil_1411's avatar
Akhil_1411
Icon for Helper IV rankHelper IV
6 years ago
Solved

Bottom N giving incorrect result

Hi All,

 

I have calculated Bottom N dax and used as a slicer. If i enter bottom 5 in slicer it is giving only 4 likewise. Can anyone help me out.

 

Dax formula : - 

Bottom Sales =
var A = RANKX(ALL(Orders[Customer Name]),CALCULATE(SUM(Orders[Sales])),,ASC,Dense)
return
IF(A<=SELECTEDVALUE('Bottom Sales'[Bottom Sales]),1,0)
 
Screenshot:-
 
Thanks,
Akhil.
General Comment
Need Help
Show and Tell
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Akhil_1411 ,

     

    I am glad that you could find the solution. You can accept your own reply to close this topic.

     

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You can try this measure instead

     

    Bottom N Sales =
VAR __slicervalue =
    SELECTEDVALUE ( 'Bottom Sales'[Bottom Sales] )
VAR __topN =
    TOPN (
        __slicervalue,
        ALL ( Orders[CustomerName] ),
        CALCULATE ( SUM ( Orders[Sales] ) ), ASC
    )
RETURN
    CALCULATE ( SUM ( Order[Sales] ), KEEPFILTERS ( __topN ) )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

    • Akhil_1411's avatar
      Akhil_1411
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi mahoneypat ,

       

      Thanks for your reply😊. The dax which u have given is giving the right result. But, when i apply the Sales amount as a slicer value it is giving the blank result. The Bottom N should also be filtered by the range of sales amount.🤔

      Please check my file.

       

      Thanks,

      Akhil.

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Try changing ALL to ALLSELECTED. Please let me know if that doesn't work.

        Regards,

        Pat