hi

[abc_777]

Hi,

How can i make a measure to find buttom N items

I know in power bi filter pane there is TOPN drop down and change and select buttom option but i want measure rather this pre filed selection as i can not use cell element to make data bar colour 

please help me  creating button N DAX measure

thanks

[PaulDBrown]

Do you have any sample data?

[Jihwan_Kim]

Hi,

Do you mean that you want to show ascending order, instead of descending order?

Please check the link down below. I hope it helps.

https://dax.guide/topn/

[abc_777]

I want to show items that are sold lowest. so management can push them to sale

[abc_777]

i need helo for creating this button or least N item as per sale value. can some one help me please

need measure

thanks

[v-yiruan-msft]

Hi @abc_777 ,

Please review the following links and check if they are what you want.

Bottom N in DAX Power BI

Bottom N Rank

If the above ones can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Best Regards

[abc_777]

hi,

as per above link or measure its comes like 

but i want like this, item name and with lowest lowest saling item quantity

I did it from power bi filter pane there is TOPN drop down and change and select buttom option but i want measure rather this pre filed selection as i can not use cell element to make data bar colour 

thanks

[abc_777]

can anyone has this solution?

please help

[abc_777]

here file attached as sample for make measure Botton N

[PaulDBrown]

Thank you for the file. Ok, so which field do you need the bottom n for? The store? the User?....

Is the n fixed (like bottom 10) or is it to be dynamic based on a slicer?

[PaulDBrown]

@abc_777 

Assuming you need this to be dynamic and that you need to show the bottom n stores by sales, you can:

Create a new unrelated table to use as the slicer to select the bottom n number using:

You can then use this measure as a filter in the filter pane for your visuals, setting the result to greater or equal to 1:

Filter Bottom n rows =
VAR _Rank =
    RANKX (
        FILTER (
            ALLSELECTED ( 'bm_retail_t ssummary'[STORE_CODE] ),
            NOT ISBLANK ( [Total Sale With VAT (Taka) (Measure)] )
        ),
        [Total Sale With VAT (Taka) (Measure)],
        ,
        ASC
    )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            _Rank <= SELECTEDVALUE ( 'Select Bottom n'[Value] )
        )
    )

or if you prefer a measure to deliver the actual sales for the bottom n, you can use:

BN Sales =
VAR _Rank =
    RANKX (
        FILTER (
            ALLSELECTED ( 'bm_retail_t ssummary'[STORE_CODE] ),
            NOT ISBLANK ( [Total Sale With VAT (Taka) (Measure)] )
        ),
        [Total Sale With VAT (Taka) (Measure)],
        ,
        ASC
    )
VAR _RowSales =
    CALCULATE (
        [Total Sale With VAT (Taka) (Measure)],
        FILTER (
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            _Rank <= SELECTEDVALUE ( 'Select Bottom n'[Value] )
        )
    )
VAR _BNsalesTotal =
    CALCULATE (
        [Total Sale With VAT (Taka) (Measure)],
        TOPN (
            SELECTEDVALUE ( 'Select Bottom n'[Value] ),
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            [Total Sale With VAT (Taka) (Measure)], ASC
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'bm_retail_t ssummary'[STORE_CODE] ),
        _RowSales,
        _BNsalesTotal
    )

to get

Sample file attached

[abc_777]

Hi @PaulDBrown ,

you are really a genius. It works man, Thank you so much for your help

But you made me greedy with this awsome solution

How can I use this dynamic Bottom N measure same way for TOP N but I want to keep unrelated table only only

when I select 1,2,3...... from unrelated table in one PBI matrix table one for Bottom N and Other PBI matrix show TOP N

is it possible with one single unrelated table??

thanks

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[PaulDBrown · Accepted solution]

Sure! Just create a new measure using the same code and change the expression in the RANKX code from ASC to DESC (and do the same if you are using the [BN Sales] measure in the TOPN code: change from ASC to DESC)

TopN Sales =
VAR _Rank =
    RANKX (
        FILTER (
            ALLSELECTED ( 'bm_retail_t ssummary'[STORE_CODE] ),
            NOT ISBLANK ( [Total Sale With VAT (Taka) (Measure)] )
        ),
        [Total Sale With VAT (Taka) (Measure)],
        ,
        DESC
    )
VAR _RowSales =
    CALCULATE (
        [Total Sale With VAT (Taka) (Measure)],
        FILTER (
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            _Rank <= SELECTEDVALUE ( 'Select Bottom n'[Value] )
        )
    )
VAR _BNsalesTotal =
    CALCULATE (
        [Total Sale With VAT (Taka) (Measure)],
        TOPN (
            SELECTEDVALUE ( 'Select Bottom n'[Value] ),
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            [Total Sale With VAT (Taka) (Measure)], DESC
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'bm_retail_t ssummary'[STORE_CODE] ),
        _RowSales,
        _BNsalesTotal
    )

Filter Top n rows =
VAR _Rank =
    RANKX (
        FILTER (
            ALLSELECTED ( 'bm_retail_t ssummary'[STORE_CODE] ),
            NOT ISBLANK ( [Total Sale With VAT (Taka) (Measure)] )
        ),
        [Total Sale With VAT (Taka) (Measure)],
        ,
        DESC
    )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'bm_retail_t ssummary'[STORE_CODE] ),
            _Rank <= SELECTEDVALUE ( 'Select Bottom n'[Value] )
        )
    )

New file attached

[abc_777]

great man

thank you so much for your time and effort and help. take care