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 isnt showing anything over $0 that is still the lowest items bought. The top 10 visual is showing too many items.

I am also using Analysis Services on a pre-built data model so my changes on the back end are limited. Thanks

 

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

3 Replies

  • 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!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    You may use the RANK() function in a measure and then filter that measure with the criteria of < 10.  To receive specific help, share some sample data to work with.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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