Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mgiusto
Helper I
Helper I

Running Total based on two values from different tables.

I have data which looks like this:

mgiusto_0-1711502814309.png

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.

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

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.

talespin_0-1711521047162.png

 

talespin_1-1711521063696.png

 

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_2-1711521248028.png

 

View solution in original post

8 REPLIES 8
talespin
Solution Sage
Solution Sage

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.

talespin_0-1711521047162.png

 

talespin_1-1711521063696.png

 

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_2-1711521248028.png

 

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?

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
 
talespin_0-1712229621089.pngtalespin_1-1712229632129.png

 

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.

can you post the sample pbix?

mgiusto
Helper I
Helper I

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_0-1711503163624.png

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.

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
lbendlin
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors