Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to Show Bottom 10 Items by Spend

Hi,  I am needing to show top 10 and bottom 10 items by ytd spend. I am currently using the filters on spend to show top N and bottom N. However, this isnt working correctly because the bottom isn...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from Ashish_Mathur  and sevenhills , please allow me to add some more information:

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _rankasc=
RANKX(
    ALLSELECTED('Table'),CALCULATE(SUM([Rand])),,ASC,Dense)
var _rankdesc=
RANKX(
    ALLSELECTED('Table'),CALCULATE(SUM([Rand])),,DESC,Dense)
RETURN
IF(
    _rankasc<=10||_rankdesc<=10,1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

     

    Best Regards,

    Liu Yang

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

sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
1 year ago
 
Use ALLSELECTED or ALL based on your needs! Please share the data that can be readable and can be used by anyone of us to develop DAX!
 
------------------------------------
 
Approach best for your needs
 
Top 10 rank measure  =
CALCULATE (
    [YTD Spend],
    KEEPFILTERS ( TOPN ( 10, ALLSELECTED ( Table1[Base Material Number] ), [YTD Spend], DESC, Dense ) )
)
 
 
Bottom 10 rank measure  =
CALCULATE (
    [YTD Spend],
    KEEPFILTERS ( TOPN ( 10, ALLSELECTED ( Table1[Base Material Number] ), [YTD Spend], ASC, Dense ) )
)
 
------------------------------------
Approach using RANKX
 
Top 10 Items =
CALCULATE ( [YTD Spend],
FILTER ( VALUES ( Table1[Base Material Number]),
IF ( RANKX ( ALL ( Table1), [YTD Spend], , DESC, Dense) <= 10, [YTD Spend], BLANK ())
)
)
 
Bottom 10 Items =
CALCULATE ( [YTD Spend],
FILTER ( VALUES ( Table1[Base Material Number]),
IF ( RANKX ( ALL ( Table1), [YTD Spend], , ASC, Dense) <= 10, [YTD Spend], BLANK ())
)
)
 
 
------------------------------------
 
Hope this helps!