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