Help with bottom N negative results

[Chris_68]

Hi,

I would require help with this troubling issue that I am currently facing.

When I am filtering based on bottom N, negative or zero results are appearing. I am tried various method suggested but it seems that none of it works. 

I want my final results to only show postive results if it is filtered by bottom N.

Link to the Power Bi file
Test.pbix
https://www.dropbox.com/scl/fi/57f6xcnflx1hhez97okwy/Test.pbix?rlkey=23mfhh7pe0c4dq6tg7rnz845r&st=xd...

Greatly appreciate for the advice given.😊

[Chris_68]

Please use this link for the Power Bi file instead

Test.pbix
https://www.dropbox.com/scl/fi/57f6xcnflx1hhez97okwy/Test.pbix?rlkey=23mfhh7pe0c4dq6tg7rnz845r&st=nf...

Apologies for the confusion.

[BA_Pete]

Hi @Chris_68 ,

Can't download the test file as blocked by org. Can you copy/paste your measure code into a code window ( </> button) here please?

Pete

[Chris_68]

Code as below

[Chris_68]

Rank = 
VAR _topItem = RANKX(ALL(Part_Sales[Item]), [Total Sales], , DESC)
VAR _bottomItem = RANKX(ALL(Part_Sales[Item]), [Total Sales], , ASC)

VAR _topMinor = RANKX(ALL(Part_Sales[Minor]), [Total Sales], , DESC)
VAR _bottomMinor = RANKX(ALL(Part_Sales[Minor]), [Total Sales], , ASC)

VAR _topMajor = RANKX(ALL(Part_Sales[Major]), [Total Sales], , DESC)
VAR _bottomMajor = RANKX(ALL(Part_Sales[Major]), [Total Sales], , ASC)

VAR _topQty = RANKX(ALL(Part_Sales[Qty]), [Total Sales], , DESC)
VAR _bottomQty = RANKX(ALL(Part_Sales[Qty]), [Total Sales], , ASC)

VAR _topCustomer = RANKX(ALL(Part_Sales[Customer Name]), [Total Sales], , DESC)
VAR _bottomCustomer = RANKX(ALL(Part_Sales[Customer Name]), [Total Sales], , ASC)

VAR _topSalesPerson = RANKX(ALL(Part_Sales[Sales Person]), [Total Sales], , DESC)
VAR _bottomSalesPerson = RANKX(ALL(Part_Sales[Sales Person]), [Total Sales], , ASC)

VAR _TopN = SELECTEDVALUE('TopN'[TopN])

VAR _RankItemSales =
    IF(
        CONTAINSSTRING(SELECTEDVALUE(Breakdown[Breakdown Fields]), "Item"),
        IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topItem, _bottomItem),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Breakdown[Breakdown Fields]), "Minor"),
            IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topMinor, _bottomMinor),
            IF(
                CONTAINSSTRING(SELECTEDVALUE(Breakdown[Breakdown Fields]), "Major"),
                IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topMajor, _bottomMajor),
                IF(
                    CONTAINSSTRING(SELECTEDVALUE(Breakdown[Breakdown Fields]), "Customer Name"),
                    IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topCustomer, _bottomCustomer),
                    IF(
                        CONTAINSSTRING(SELECTEDVALUE(Breakdown[Breakdown Fields]), "Sales Person"),
                        IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topSalesPerson, _bottomSalesPerson),
                        IF(SELECTEDVALUE(TopBottom[Value]) = "Top", _topQty, _bottomQty)
                    )
                )
            )
        )
    )

RETURN
    IF(_RankItemSales <= _TopN, [Total Sales])

[BA_Pete]

Cool, thanks.

You should just be able to exclude these values right at the end of the measure, something like this (assuming you also don't want to keep negative values when selecting TOPN):

RETURN
    IF(
        _RankItemSales <= _TopN && [Total Sales] >= 0,
        [Total Sales]
    )

Pete

[Chris_68]

This is great.

Are we able to exclude 0 value as well?

I tried removing the = sign but apparently it does not work.

[BA_Pete]

Hi @Chris_68 ,

This should work:

RETURN
    IF(
        _RankItemSales <= _TopN && [Total Sales] > 0,  // Removed =, like you tried already?
        [Total Sales]
    )

If removing the = does not work, then I suspect you have very small numbers in your sales data that are getting past the filter e.g. 0.00001 etc.

There's two basic ways to handle this:

1) Truncate/round all of your source sales data to two decimal places IN POWER QUERY (Number.Round), or earlier in the pipeline (SQL Server etc.) if you can. Making this change in the front-end/model will not have the desired effect as this will just change how the values are displayed, not the underlying values themselves.

2) Based on the materiality of these fractional values, you could potentially adjust the filter slightly to && [Total Sales] > 0.001 or similar, but this is not the preferred option.

Pete

[Chris_68]

Hi Pete,

You're right—it may be working, but it looks like entries with a value of 0 aren't showing in the table. For example, when I select Top 10, nothing appears. But with Top 20, only 8 customers show up.

Would appreciate any suggestions you have.

[BA_Pete · Accepted solution]

Ah, ok, my bad. It's evaluating the rank first, then filtering afterwards.

You'll need to apply the filter prior to each rank evaluation, something like this:

VAR _topItem =
    RANKX(
        //Filter table rank is evaluated over
        FILTER( ALL( Part_Sales[Item] ), [Total Sales] >= 0 ),
        [Total Sales],
        ,
        DESC
    )

Check whether this one works as required and, if it does, you'll need to apply a similar filter to each evaluation table in your rank variables.

Pete

[Chris_68]

Hi Pete,

Great work! It is working now.

Thank you.

[Chris_68]

https://1drv.ms/u/c/395f6dba7fbed437/EXA7eIPIqNNAgsno9KL2eg0B93aBukjDjsuQtFvES_gVBA?e=qOFdMa

location of download