PowerBI Running Total over large number of values

Question

I am attempting to create a DAX running total on a dataset with 30,000 unique values. I've tried variations on the standard logic pasted below. This logic works fine for a few thousand unique values; but runs for a long time and runs out of memory on datasets with larger numbers of unique values.

atest2 = 

VAR selectedPosition = SELECTEDVALUE(ptSKUSummary[RankEq UOH2s])

VAR cumSumUOM = 
CALCULATE(
        SUM(ptSKUSummary[Eq UOH2s]),
        ptSKUSummary[RankEq UOH2s] <= selectedPosition,
        ALLSELECTED(ptSKUSummary[RankEq UOH2s])
)

RETURN cumSumUOM`

Edit: I really like the thorough O-complexity answer provided by @Luke_O. It did answer the question I was asking; so I think I will give it an answer status. However, it did not answer the question I meant to ask (completely my mistake, I'm still figuring my way around DAX and asked a simplified version of my question, without the appropriate background to it).

I'm going to make another attempt to ask the question again, and provide the solution that I eventually came up with.

For background, I'm building a report application for end users that has 38 different base measures (by which I mean numeric fields in the central fact table that can be summed to create meaningful results); the end users want to be able to select which measure to use in the report.

The data source is a database with a standardized table structure (tables & fields); but the content will vary from project to project. The end users can import their data into a PBI template (.pbit file) for their project and have a variety of reports available to them.

The specific chart I was struggling with was a Pareto (aka 80-20) chart with the percentage of entities (in this case SKUs) on the x-axis and percentage of units on the y-axis. I've pasted a sample graph below.

All of which may not even be relevant (I will get better at asking questions). The actual problem at hand was that for the most recent project uploaded I had a 13 million record fact table with 30,000 unique SKUs in it. When I attempted to create a Pareto chart using every individual SKU (that is to say the x-axis advanced in increments of 0.0033%) the percentage of units (the cumulative sum of how many units sold up to the the SKU represented on the x-axis, with SKUs sorted in descending order by the sum of units sold, divided by the total units sold across all SKUs) calculation would run for 20 minutes or so, and then return an out of memory error.

My current solution is to change the 30,000 SKUs into 1,000 logical buckets (increments of 0.1%) before performing the cumulative sum calculation.

I know that was all way to many words for most people to want to take the time to read it. But here is the DAX code I finally came up with to solve this problem.

msPercUOMP4 = 

VAR selectedPercentile = SELECTEDVALUE(ptParetoPercentileP2[Percent of SKUs])
VAR numSKUs = CALCULATE( [msUniqueSKUs], ALLSELECTED(ptOrderFile) )
VAR totalVolume = CALCULATE( [msSumUOM], ALLSELECTED(ptOrderFile) )

VAR vtSKUsummary = 
SUMMARIZE(
    ALLSELECTED(ptOrderFile),
    ptOrderFile[SKU],
    "@Volume", [msSumUOM]
)

VAR vtSKUsummaryRanked = 
ADDCOLUMNS(
    vtSKUsummary,
    "@Rank", ROWNUMBER(vtSKUsummary, ORDERBY([@Volume], DESC, ptOrderFile[SKU], ASC))
)

VAR vtSKUpercentiles = 
ADDCOLUMNS(
    vtSKUsummaryRanked,
    "@Percentile", ROUND(DIVIDE([@Rank], numSKUs), 3)
)

VAR prevSelectedVolume =
SUMX(
    FILTER(vtSKUpercentiles, [@Percentile] <= selectedPercentile-0.001),
    [@Volume]
)

VAR selectedVolume = 
SUMX(
    FILTER(vtSKUpercentiles, [@Percentile] <= selectedPercentile),
    [@Volume]
)

VAR result = DIVIDE(selectedVolume, totalVolume, 0 )

RETURN 
IF( prevSelectedVolume = selectedVolume, BLANK(), IF(result = BLANK(), 0, result) )

Answer 1

You are a lucky man. Not six months ago in the Dec 2022 release of Power BI, window functions were introduced. These allow you to sample a "window" of your data efficiently without having to go through normal filters.

Up until now, the most efficient method would have been FILTER(..., [RANK] <= EARLIER( [RANK] ) ) or a PowerQuery List.Generate(). I just ran the following test on my 64gb RAM, i9-9980HK (5.00 GHz max) machine, and the first method took 262,033 ms to run.

series = GENERATESERIES( 1, 2^15 ) //32,768 rows

CALCULATE(
    SUM( series[Value] ),
    series[Value] <= EARLIER( series[Value] )
)

No bueno.

Allow me to introduce the WINDOW() function. This gem did the same work in 23,520 ms. Granted, that is still almost half a minute, but that is 2.4 orders of magnitude faster.

SUMX( 
    WINDOW( 1, ABS, 0, REL, ORDERBY( series[value], ASC ) ),
    series[value]
)

How this works:

• Start at absolute 1 (first row of the table).
• Open the window to relative 0 (i.e., the current row).
• Do this by sorting by the value (rank) ascending.

That's it. This function even allows for partitioning (running totals by category), tie handling and more.

It's a work of art. Knock yourself out.

---

This is an interesting case to look at from an O-complexity perspective. I ran the same test between the two functions on {2^{8...15}}. Clearly, the WINDOW() function is much more scalable. But how much more?

When this is plotted on a log axis, we can see that both functions are operating in the exponential domain ( O(2^n) ). However, for every 32x increase in data size, FILTER() time increases by 2^10.8x, whereas WINDOWS() time increases by 2^9.7x, meaning efficiency more than doubles

---

Benchmark data below (time in ms). Each datum is an average of 5 cold cache executions.

log(n)|     n|WINDOW()|FILTER()
------+------+--------+--------
    15|32,768|  23,520| 262,033
    14|16,384|   5,955|  55,362
    13| 8,192|   1,438|  12,014
    12| 4,096|     378|   2,865
    11| 2,048|     101|     640
    10| 1,024|      28|     147
     9|   512|      10|      38