How to Show Bottom 10 Items by Spend

[Libbyb23]

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

[v-yangliu-msft · Accepted solution]

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

Hi  @Libbyb23 ,

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

[Ashish_Mathur]

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.

[sevenhills]

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!