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 ...
Need Help
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])))
 
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Share the download link of the PBI file.

    • barthand16's avatar
      barthand16
      Frequent Visitor
      3 years ago

      This has confidential information in the workbook. I cannot share. I beleive the issue is dependent on the rows that are in the Viz. Anything else I can provide? 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        If you can, please share a redacted version of the PBI file.