cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
Solution Sage

hi @mgiusto

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 8
Solution Sage

hi @mgiusto

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

Helper I

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?

Solution Sage

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.

Super User

can you post the sample pbix?

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

Super User

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

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors