Running Total based on two values from different tables.

[mgiusto]

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 · Accepted solution]

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]

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?

[talespin]

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.

[lbendlin]

can you post the sample pbix?

[mgiusto]

org	code	date	ord_qty	on_hand	Run Tot
202	50662	3/8/2024	3	528	525
202	50662	3/14/2024	10	528	515
202	50662	3/15/2024	1	528	514
202	50662	3/20/2024	2	528	512
202	29314	3/15/2024	1	2142	2141
202	29314	3/20/2024	1	2142	2140
202	74021	2/6/2024	3	11770	11767
202	74021	2/16/2024	24	11770	11143
202	74021	2/28/2024	4	11770	11139
202	74021	2/29/2024	1	11770	11138
202	74021	3/12/2024	13	11770	11125
202	74021	3/20/2024	2	11770	11123
202	11293	3/21/2024	2	3336	3334
202	25796	3/19/2024	1	8	7

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]

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]

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]

org	code	date	ord_qty
202	50662	3/8/2024	3
202	50662	3/14/2024	10
202	50662	3/15/2024	1
202	50662	3/20/2024	2
202	29314	3/15/2024	1
202	29314	3/20/2024	1
202	74021	2/6/2024	3
202	74021	2/16/2024	24
202	74021	2/28/2024	4
202	74021	2/29/2024	1
202	74021	3/12/2024	13
202	74021	3/20/2024	2
202	11293	3/21/2024	2
202	25796	3/19/2024	1

[on_hand]

org	code	on_hand
202	50662	528
202	29314	2142
202	74021	11770
202	11293	3336
202	25796	8

[lbendlin]

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-...

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/1447...