Forum Discussion

mgiusto's avatar
mgiusto
Icon for Helper I rankHelper 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
     

     

mgiusto's avatar
mgiusto
Icon for Helper I rankHelper 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
Icon for Super User rankSuper User
2 years ago

can you post the sample pbix?