Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
3 years ago
Solved

sum values's measure below zero

Hello,

I created this formula,

I first calculate the cummulated value by date, and then I want to extract the values that are below zero.

 

Measure = 

VAR proj=CALCULATE(SUM(Data[Qty]),FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX('Date list'[Date])))+0

VAR tab=SUMMARIZE(Data,Data[Material],"tot",proj)

RETURN CALCULATE(SUMX(FILTER((tab),[tot]<0),proj))

 

this is the output: the formula return the value below zero only when there is a movement pushing the cummulative below zero.

The following days, there are no movements, but the "Mesure" shows nothing.
I would expect to see zeros or negative values.

 

any idea how to show -64 on the following days?

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi MagikJukas 

    try like:

    Measure =
    VAR proj=
    CALCULATE(SUM(Data[Qty]),FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX('Date list'[Date])))+0
    VAR tab=
    SUMMARIZE(Data,Data[Material],"tot",proj)
    VAR _value =
    CALCULATE(SUMX(FILTER((tab),[tot<0),proj))
    VAR _value2=SUM(Data[Qty])
    RETURN
    IF(_value = BLANK(), _value2, _value)

    • MagikJukas's avatar
      MagikJukas
      Icon for Resolver III rankResolver III
      3 years ago

      thank you, but it does not change much.

       

      the problem is how the "proj" function is consolidated. Data[Qty] have both positive and negative values.

      It seems that with SUMX it considers all the [Qty] that are negative. Instead, I am looking for the "proj" which is the sum of the [Qty].