Bottom N giving incorrect result

[Akhil_1411]

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)

Power BI File : https://we.tl/t-qHmuvNqORq

Screenshot:-

Thanks,

Akhil.

[mahoneypat]

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]

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]

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

Regards,

Pat

[Akhil_1411]

@mahoneypat , Nope😐

[mahoneypat]

Ok.  VALUES( ) around Customer Name instead of ALL( ) should do it.

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]

It is not working with Values(). But, thanks for your time.😊

I have re-written the dax now it is giving the expected result.

Bottom Sales =

var A = RANKX(ALLSELECTED(Orders[Customer Name]),CALCULATE(SUM(Orders[Sales])),,ASC,Dense)

return

IF(ISFILTERED(Orders[sales customer]),IF(A<=SELECTEDVALUE('Bottom Sales'[Bottom Sales])+1,SUM(Orders[Sales]),BLANK()),IF(A<=SELECTEDVALUE('Bottom Sales'[Bottom Sales]),SUM(Orders[Sales]),BLANK())

)

Thanks,

Akhil.

[v-eachen-msft · Accepted solution]

Hi @Akhil_1411 ,

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