Forum Discussion

ClaudioF's avatar
ClaudioF
Icon for Helper II rankHelper 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
ClaudioF's avatar
ClaudioF
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper II rankHelper 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...

    • techies's avatar
      techies
      Icon for Super User rankSuper User
      1 year ago

      Hi ClaudioF as i understand,  

      resultmeasure = VAR StockLimit = 21
      VAR CurrentOrder = SELECTEDVALUE('Sheet3'[order number])

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

      RETURN
      IF(StockLimit - RunningTotal >= 0, "available", "out")
       
      Hope it works, please share pbix file if possible