Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chunkysoup
Frequent Visitor

Measure to find rows with a negative value in a cell

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

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

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

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'[itemis 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

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.