Forum Discussion

Nick_2510's avatar
Nick_2510
Icon for Helper I rankHelper I
4 years ago
Solved

count all negative numbers

I want to count all negative numbers by rows and in total...but it doesn't work in total

M =
SUMX ( VALUES ( Data[Name] ), IF ( [diff_AvSal_bal] < -1, 1, 0))

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Nick_2510 
    Please try

    Count of -ve =
SUMX (
    CROSSJOIN ( VALUES ( 'Date'[Date] ), VALUES ( Data[Name] ) ),
    IF ( [diff_AvSal_bal] < -1, 1, 0 )
)

20 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Nick_2510 
    Please try

    Count of -ve =
SUMX (
    CROSSJOIN ( VALUES ( 'Date'[Date] ), VALUES ( Data[Name] ) ),
    IF ( [diff_AvSal_bal] < -1, 1, 0 )
)
    • Nick_2510's avatar
      Nick_2510
      Icon for Helper I rankHelper I
      4 years ago

      Thx a lot! It works!


      But now I need to add one option in IF constraction...

      switch off name if name in a period - data for this in another table with colomns (Name, Start_period, End_Period)

       

      Count of -ve =

      SUMX (
      CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ) ),
      IF ( [diff_AvSal_bal] >0 OR 'Календарь'[Date] IN DATESBETWEEN('Sheet1'[Start_per], 'Sheet1'[End_per], 0, 1)
      )


      • Nick_2510's avatar
        Nick_2510
        Icon for Helper I rankHelper I
        4 years ago
        SUMX (
        CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ), 'delist'),
        IF ( [diff_AvSal_bal] >= 0 || ('Календарь'[Date] >= 'delist'[Start_per] && 'Календарь'[Date] <= 'delist'[End_per]), 0, 1 )
        )

        How to connect Name in first table and in Second (Start_per, End_per)...now it puts 0 to every name which is in that period(
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Nick_2510 

    Please try

    Count of -ve =
SUMX ( VALUES ( Data[Name] ), CALCULATE ( IF ( [diff_AvSal_bal] < -1, 1, 0 ) ) )
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Nick_2510 
        Do you have other active filters of the page or on the visual? any slicers? Any other table or other column invloved?