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

BA_Pete's avatar
BA_Pete
Super User
1 year ago

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'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.