Streamline Cumulative Total Measure

Question

Hey Everyone,&nbsp;I have 2 essentially identical measures that give the cumulative total of a bank account's balance based on all of the transactions as of that day. The formula is as follows:Running Total Base Amount = CALCULATE (&nbsp; &nbsp; SUM ( VW_KPI_BANK_TRANSACTION[Base Amount] ),&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; ALL ( VW_KPI_BANK_TRANSACTION),&nbsp; &nbsp; &nbsp; &nbsp; (VW_KPI_BANK_TRANSACTION[Bank Balance Transaction Date Filter]) &lt;= MAX (VW_KPI_BANK_TRANSACTION[Bank Balance Transaction Date Filter])&nbsp; &nbsp; ))The formula works, but takes forever to load, wondering if there is any way to optimtize this formula for a shorter loading time.&nbsp;Thanks!

anonymous · Answer

Hi&nbsp;query_lord_1&nbsp;,
&nbsp;
You may try SUMX&nbsp;function instead of SUM function. In general, when working with large data sets, the SUMX function is more efficient than the SUM function because it computes row by row instead of aggregating the data first.
Running Total Base Amount =
SUMX (
    FILTER (
        ALL ( VW_KPI_BANK_TRANSACTION ),
        ( VW_KPI_BANK_TRANSACTION[Bank Balance Transaction Date Filter] )
            &lt;= MAX ( VW_KPI_BANK_TRANSACTION[Bank Balance Transaction Date Filter] )
    ),
    VW_KPI_BANK_TRANSACTION[Base Amount]
)

&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

query_lord_1 · Answer

This does not fix my problem. I've tried consolidating the rows (prev 1.5m) down to just after a certain date and only using a total for each day rather than haveing all individual transactions by usng SUMMARIZE to group them by that criteria in a different table. I am now&nbsp; down to 50k rows, but still takes too long to load&nbsp;

Forum Discussion

Streamline Cumulative Total Measure

2 Replies

Related content

Cumulative Difference from Another measure

Cumulative Measure

Cumulative Total

Transform measure in cumulative measure

Cumulative measure / 100