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

Frequent Visitor

## Calculating and displaying max of a running total

I have the following source data

 Account Joined Date Transaction Date Transaction Value Account A 08/10/2019 14/10/2019 \$2600.00 Account A 08/10/2019 24/10/2019 -\$650.00 Account A 08/10/2019 22/11/2019 -\$650.00 Account A 08/10/2019 25/11/2019 \$650.00 Account A 08/10/2019 10/12/2019 \$650.00 Account A 08/10/2019 24/12/2019 -\$650.00 Account B 09/10/2019 06/12/2019 \$30.58 Account B 09/10/2019 18/12/2019 \$214.38 Account B 09/10/2019 24/12/2019 -\$244.96 ... ... ... ...

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

 Account Joined Date Max Balance Total Funds Disbursed % Account A 08/10/2019 \$2600.00 -\$1950.00 75 Account B 09/10/2019 \$244.96 -\$244.96 100 ... ... ... ... ..

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
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
7 REPLIES 7
Super User

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

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

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.

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
Community Champion

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

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

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

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(
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
Frequent Visitor

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?

Community Champion

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