Forum Discussion

mgiusto's avatar
mgiusto
Helper I
2 years ago
Solved

Running Total based on two values from different tables.

I have data which looks like this: The first 4 columns are in a table named [orders] The last column is in a table named [on_hand] this table also has the [org] and [code] field so it can lin...
  • talespin's avatar
    talespin
    2 years ago

    hi mgiusto 

     

    Please try this

     

    Step1 : Created tables based on data shared by you, please note that I have kept all datatypes as whole number except date which is Date Datatype. I have not linked these two tables in data model.

     

     

    Step2 : 

    Create this measure.

     

    On Hand =
    VAR _SelOrg = SELECTEDVALUE(Orders[org])
    VAR _selCode = SELECTEDVALUE(Orders[code])
    VAR _SelDate = SELECTEDVALUE(Orders[Date])
    VAR _OnHandQty = SELECTCOLUMNS(FILTER(OnHand, OnHand[org] = _SelOrg && OnHand[code] = _selCode), "@OnHand", OnHand[on_hand])
    VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]), Orders[Date] <= _SelDate)

    RETURN _OnHandQty - _SumQty
     

     

talespin's avatar
talespin
Solution Sage
2 years ago

hi mgiusto 

 

Please try this

 

Step1 : Created tables based on data shared by you, please note that I have kept all datatypes as whole number except date which is Date Datatype. I have not linked these two tables in data model.

 

 

Step2 : 

Create this measure.

 

On Hand =
VAR _SelOrg = SELECTEDVALUE(Orders[org])
VAR _selCode = SELECTEDVALUE(Orders[code])
VAR _SelDate = SELECTEDVALUE(Orders[Date])
VAR _OnHandQty = SELECTCOLUMNS(FILTER(OnHand, OnHand[org] = _SelOrg && OnHand[code] = _selCode), "@OnHand", OnHand[on_hand])
VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]), Orders[Date] <= _SelDate)

RETURN _OnHandQty - _SumQty
 

 

  • mgiusto's avatar
    mgiusto
    Helper I
    2 years ago

    So this kinda works, as long as the data in my grid stays sorted exactly the same order, if the sort changes the running total goes bonkers. But here's the other problem this measure created. The time it takes for this DAX to run is less than ideal. My data table is 600 records and for this to load it takes over 20 seconds or so to see the grid appear.

     

    I'm going to see if there is a way to create this running total on the DB side so the data is already in my table rather than trying to have it calc on the fly via a measure.  Would you have any advice on how to do that?

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      can you post the sample pbix?

    • talespin's avatar
      talespin
      Solution Sage
      2 years ago

      hi mgiusto 

       

      If its 600 records, it shouldn't take that much time. There are other options.

       

      In power query, merge Orders and OnHand table on "org" and "code" and bring in OnHand total into Orders table.

       

      You can use calculated column

      ---------------------------------------------

      CalCol On Hand =
      VAR _Org = Orders[org]
      VAR _Code = Orders[code]
      VAR _OnHandQty = Orders[on_hand]
      VAR _OrdDate = Orders[Date]
      VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), Orders[org] = _Org && Orders[code] = _Code && Orders[Date] <= _OrdDate)

      RETURN _OnHandQty - _SumQty

       

      Or you can use a measure

      ---------------------------------------------

      On Hand =
      VAR _SelOrg = SELECTEDVALUE(Orders[org])
      VAR _selCode = SELECTEDVALUE(Orders[code])
      VAR _SelDate = SELECTEDVALUE(Orders[Date])
      VAR _OnHandQty = CALCULATE( MAX(Orders[on_hand]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]) )
      VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]), Orders[Date] <= _SelDate)

      RETURN _OnHandQty - _SumQty
       

       

      If above solution does not work for you and you still want SQL.

       

      Select o.org, o.code, o.Order_Date, oh.on_hand_tot,
      (oh.on_hand_tot - SUM(o.ord_qty) OVER(PARTITION BY o.org, o.code ORDER BY o.Order_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) "Runningtot"
      from Orders o
      inner join OnHand oh
      on o.org = oh.Org AND o.code = oh.code

       

       

       

      Please note that running total is calculated per group(org and code) and in orderdate ascending order, if you change sort order of these three column, running total will still be correct but result will look like bonkers 😄 if that is not the case please share pbix file with same data as you shared in your first post.