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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
joemuziki
Frequent Visitor

Calculating and displaying max of a running total

I have the following source data 

 

AccountJoined Date Transaction DateTransaction Value
Account A08/10/201914/10/2019$2600.00
Account A08/10/201924/10/2019-$650.00
Account A08/10/201922/11/2019-$650.00
Account A08/10/201925/11/2019$650.00
Account A08/10/201910/12/2019$650.00
Account A08/10/201924/12/2019-$650.00
Account B09/10/201906/12/2019$30.58
Account B09/10/201918/12/2019$214.38
Account B09/10/201924/12/2019-$244.96
............

 

I am trying to create this final result table in Power BI:

 

Account Joined DateMax BalanceTotal Funds Disbursed%
Account A08/10/2019$2600.00-$1950.0075
Account B 09/10/2019$244.96-$244.96100
..............

 

Where:

  1. Max Balance = Max of running total of 'Transaction Value'
  2. Total Funds Disbursed = Total of negative transaction values of an account 
  3. % = ( Total Funds Disbursed / Max balance )

How best can I create this table visual in Power BI Desktop? What is the correct correct DAX to generate the 3 desired columns?

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

Hi @joemuziki 

try this technique:

1. create a measure in your table

Measure = 
calculate(sum('Table1'[Transaction Value]);filter(allexcept('Table1';Table1[Account]);Table1[Transaction Date]<=SELECTEDVALUE(Table1[Transaction Date])))

2. create a calculated table

Table = 
addcolumns(
SUMMARIZE(
'Table1';Table1[Account];Table1[Joined Date ];
"Max Balance";MAXX(Table1;[Measure]);
"Total Funds Disbursed";sumx(Table1;calculate(sum(Table1[Transaction Value]);Table1[Transaction Value]<0))
);
"%";ABS(DIVIDE([Total Funds Disbursed];[Max Balance]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@joemuziki not sure about your business logic but this what you can try

 

add new column:

 

 

Abs = ABS ( 'Table'[Transaction Value] )

 

 

add new measures

 

Max Balance = MAX ( 'Table'[Abs] )

Disbursed = CALCULATE ( SUM ( 'Table'[Transaction Value] ), 'Table'[Transaction Value] < 0 )

% = DIVIDE( ABS( [Disbursed] ), [Max Balance] )

 

 

here is the output    . Would appreciate Kudos 🙂 if my solution helped.

 

image.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

az38
Community Champion
Community Champion

hi @parry2k 

beautiful, but not sure it will work

Disbursed = CALCULATE ( SUM ( 'Table'[Transaction Value] ), 'Table'[Transaction Value] < 0 )

as it could be a lot unpredictable +-+---+--+--+ transaction, and we need max cumulative Amount on the account. So, as I see, we need a maximum sum on the account for its life time

do not hesitate to give a kudo to useful posts and mark solutions as solution

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

Hi @joemuziki 

try this technique:

1. create a measure in your table

Measure = 
calculate(sum('Table1'[Transaction Value]);filter(allexcept('Table1';Table1[Account]);Table1[Transaction Date]<=SELECTEDVALUE(Table1[Transaction Date])))

2. create a calculated table

Table = 
addcolumns(
SUMMARIZE(
'Table1';Table1[Account];Table1[Joined Date ];
"Max Balance";MAXX(Table1;[Measure]);
"Total Funds Disbursed";sumx(Table1;calculate(sum(Table1[Transaction Value]);Table1[Transaction Value]<0))
);
"%";ABS(DIVIDE([Total Funds Disbursed];[Max Balance]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thanks @az38 this is definitely on the right track. I am unable to completely verify as the table is bringing back too many results and is breaching the Power BI limit. How can I apply a filter that limits the results of the summary table to:

1. % > 40%

2. Transactions Dates in the last 90 days

 

Thanks in advance

az38
Community Champion
Community Champion

@joemuziki
Try

 

Measure =
calculate(sum('Table1'[Transaction Value]);filter(allexcept('Table1';Table1[Account]);Table1[Transaction Date]<=SELECTEDVALUE(Table1[Transaction Date]) && datediff(Table1[Transaction Date];today();day) <= 90))

 

And

 

Table =
Filter(
addcolumns(
SUMMARIZE(
'Table1';Table1[Account];Table1[Joined Date ];
"Max Balance";MAXX(Table1;[Measure]);
"Total Funds Disbursed";sumx(Table1;calculate(sum(Table1[Transaction Value]);Table1[Transaction Value]<0))
);
"%";ABS(DIVIDE([Total Funds Disbursed];[Max Balance]))
);
[%]>0.4
)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

So this has been working like a charm @az38 , thank you very much. I am however bumping into an issue when I change the 'Transaction Date' column from a date to a date-time column in order to calculate the intraday running balance. I am getting an error that I am exceeding the 1000000 row limit. The underlying data set has not changed. Any thoughts on how to resolve this? 

az38
Community Champion
Community Champion

Hi @joemuziki 

see here https://docs.microsoft.com/en-us/power-bi/service-get-data 

 


Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. Your underlying data can still contain more than one million rows. You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.

so, first be sure you are really get less then million rows or change your data source query

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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