Measure to find rows with a negative value in a cell

[chunkysoup]

Hello all, I am trying to find the sales quantity of certain items that came in with a zero or less than zero list price. Initially I wrote this to just validate
2020 Neg List Price =
    IF(CONTAINS('2020','2020'[Current List USD],0),SUM('2020'[Sales Quantity]))

It worked but it ONLY finds those rows with exactly 0 in the list price column. I need to find those and negative values.

As you likely know, CONTAINS doesn't like evaluation expressions like <=. I tried an If('2020'[Current List USD] <=0 but PowerBi tells me it can't find the column but the column exists and works. All it shows me in the quick properties are the other measures I have written when I start typing it. I have tried counts and Calculate(DISTINCTCOUNT but I am not getting values that make any sense.

I would very much appreciate if you could point me in the right direction here.

Thanks

[v-deddai1-msft]

Hi @chunkysoup ,

According to your description, would you please refer to the measure:

2020 Neg List Price =

IF (

    MAX ( '2020'[Current List USD] ) <= 0,

    SUMX (

        SUMMARIZE (

            '2020',

            '2020'[item],

            "_SUM", SUM ( '2020'[Sales Quantity] )

        ),

        [_SUM]

    ),

    BLANK ()

)

The '2020'[item] is your item column, you need to modify it according to your actual column name.

If it doesn't meet your requirement, Could you please inform us more detailed information, such as  your sample data(by using OneDrive for Business) if possible? Then we will help you more correctly. Please don't contain any Confidential Information or Real data in your reply or files.

Best Regards,

Dedmon Dai

[chunkysoup · Accepted solution]

Hu Dedmon,

Unfortunately, that did not quite solve the problem but it did lead me to look into other functions so thank you. I believe I solved the issue this way.

2020 Neg List = 
SUMX(FILTER('2020', '2020'[Current List USD] <=0), [Sales Quantity])

Again thank you for replying and leading me down a path where I believe I was able to solve my issue.

[HotChilli]

It looks like you are after the sum of sales quantity for all item whose list price is less than or equal to zero.

So you'll need a measure. It's going to look for the SUM of sales quantity, and you're going to filter the table for price <= 0.