Forum Discussion

ClaudioF's avatar
ClaudioF
Helper II
1 year ago

Complex conditional summing

Hey everyone! how you doing? I have been now a month trying to make this logic work in dax but no solution. Cna anyone please help me with this? or even just say that power Bi will not be able of d...
"advanced"
"MEASURE"
Conditional Sum
Cumulative Sum
techies's avatar
techies
Super User
1 year ago

Hi ClaudioF please try this, add as a calculated column

 

result column = VAR StockLimit = 21
var CurrentRow = 'Sheet3'[Quant. Falta]

VAR RunningTotal =
    SUMX(
        FILTER(
            ALL('Sheet3'),
            'Sheet3'[Produto] = EARLIER('Sheet3'[Produto]) &&
            'Sheet3'[Quant. Falta] <= CurrentRow
        ),
        'Sheet3'[Quant. Falta]
    )

RETURN
IF(RunningTotal <= StockLimit, "available", "out")
  • ClaudioF's avatar
    ClaudioF
    Helper II
    1 year ago

    Hi techies , I can't do as calculated column because I need it to be donamic, when I filter by date for example, it will do the same logic but only with the filtered orders in that visual..

    • techies's avatar
      techies
      Super User
      1 year ago

      ok , try this

       

      ResultMeasure =
      VAR StockLimit = 21
      VAR CurrentRow = SELECTEDVALUE('Sheet3'[Quant. Falta])

      VAR RunningTotal =
          SUMX(
              FILTER(
                  ALLSELECTED('Sheet3'),
                  'Sheet3'[Produto] = SELECTEDVALUE('Sheet3'[Produto]) &&
                  'Sheet3'[Quant. Falta] <= CurrentRow
              ),
              'Sheet3'[Quant. Falta]
          )

      RETURN
      IF(RunningTotal <= StockLimit, "available", "out")
      • ClaudioF's avatar
        ClaudioF
        Helper II
        1 year ago

        im sorry for the missing information, but the trick of this all is also that this might be distribute to the earliest order then tto the oldest, each of those lines is a order with number, 111, 222, 333, 444, 555, 666, 777, 888, in order...