Forum Discussion

Chris_68's avatar
Chris_68
Helper I
1 year ago
Solved

Help with bottom N negative results

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 suggeste...
Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

     

    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's avatar
Chris_68
Helper I
1 year ago

Code as below

Chris_68's avatar
Chris_68
Helper I
1 year ago
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])
  • Chris_68's avatar
    Chris_68
    Helper I
    1 year ago
    https://1drv.ms/u/c/395f6dba7fbed437/EXA7eIPIqNNAgsno9KL2eg0B93aBukjDjsuQtFvES_gVBA?e=qOFdMa

    location of download 

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

     

    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's avatar
      Chris_68
      Helper I
      1 year ago

      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's avatar
        BA_Pete
        Super User
        1 year ago

        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