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 link to the [orders] table.

 

What I would like is a new column as a running total which will subtract the [ord_qty] from the first [on_hand] total then each row from there based on [org], [code] & [date] (date ascending) have this running total continue to decrease.

 

So in the above the rows with 528 in [on_hand] should be 525 then 515, 514 and finally 512. I should not even see the 528 total because I need to deduct that first 3 from it.

 

I have no clue how to write this in DAX, any help would be greatly appreciated.

  • 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
     

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • mgiusto's avatar
    mgiusto
    Icon for Helper I rankHelper I
    2 years ago
    orgcodedateord_qtyon_handRun Tot
    202506623/8/20243528525
    202506623/14/202410528515
    202506623/15/20241528514
    202506623/20/20242528512
    202293143/15/2024121422141
    202293143/20/2024121422140
    202740212/6/202431177011767
    202740212/16/2024241177011143
    202740212/28/202441177011139
    202740212/29/202411177011138
    202740213/12/2024131177011125
    202740213/20/202421177011123
    202112933/21/2024233363334
    202257963/19/2024187

     

    Here is some sample data, I have added the last column Run Tot to show the desired result.

    Reminder, the On Hand is in a different table and does not exist in the [order] table.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      RT = 
var md = max('Table'[date])
return max('Table'[on_hand])-CALCULATE(sum('Table'[ord_qty]),'Table'[date]<=md)

       

      You can adjust the formula to provide the on_hand lookup differently.

      • mgiusto's avatar
        mgiusto
        Icon for Helper I rankHelper I
        2 years ago

        This does not work as it does not take into account that you need to match the [on_hand] records to the [ord_qty] records based on [org] and [code] being equal in both tables.

         

        Here's what the two tables look like:

        [orders]

        orgcodedateord_qty
        202506623/8/20243
        202506623/14/202410
        202506623/15/20241
        202506623/20/20242
        202293143/15/20241
        202293143/20/20241
        202740212/6/20243
        202740212/16/202424
        202740212/28/20244
        202740212/29/20241
        202740213/12/202413
        202740213/20/20242
        202112933/21/20242
        202257963/19/20241

         

        [on_hand]

        orgcodeon_hand
        20250662528
        202293142142
        2027402111770
        202112933336
        202257968
  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution 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
      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?