Forum Discussion

barthand16's avatar
barthand16
Frequent Visitor
3 years ago

Need Help with running total. Getting incorrect sum within the subtotal.

I have the following formula which works great within the matrix. 

Running =
VAR StartingOnhand = CALCULATE(Demand[Sum Onhand], ALL('calendar'[Period]))
RETURN
    IF (
        ISFILTERED ( 'calendar'[Period] ),
        CALCULATE (
            SUM ( 'Demand'[Inventory Table] ),
            FILTER (
                ALLSELECTED ( 'calendar'[Period] ),
                ISONORAFTER ( 'calendar'[Period], MAX ( 'calendar'[Period] ), DESC )
            )
        ) + StartingOnhand,
        StartingOnhand
    )

 

However as you can tell the subtotal in my matrix is not calualting correctly. For example "period" 2320" should equal 956 (149+807)

Please let me know if you have any questions. 
 
**Note that the two rows "Material" are filtered from a total of 400 rows.**
 
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    barthand16 , You need measures like , I have both addition and subtraction and Intial Inventory is a measure

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

    • barthand16's avatar
      barthand16
      Frequent Visitor
      3 years ago

      As can be seen using the first fomula I get blanks. As well the total is not correct. For instance 2320 should equal 256 not 113. My orginal fomula worked great I just need something that will sum it correct. Also the second formula I kept getting a error that real firstnonblank value needs 2 items. 

       
      Formula used for the above picture
       
      Measure 2 = [Sum Onhand]+CALCULATE(SUM(Demand[Demand]),filter('calendar','calendar'[Period]<=MAXX('calendar','calendar'[Period])))+CALCULATE(SUM(Demand[Confirmed Qty]),FILTER('calendar','calendar'[Period]<=MAXX('calendar','calendar'[Period])))