Cummulative sum with slicer, big table

Hi Brise ,

It looks like you have a Power BI table with slicers filtering by Date, ID, and Hour, and you need to calculate a cumulative sum of Monto while respecting these slicers. Given that your dataset contains 1.6 billion rows, optimizing this calculation is crucial to avoid performance issues.

Your current approach using ALLSELECTED may be causing unnecessary memory consumption and performance bottlenecks because it expands the dataset beyond the slicer filters. Instead, using KEEPFILTERS ensures that the slicer selections remain active while applying the cumulative sum logic.

Here’s an optimized DAX measure that calculates the cumulative sum efficiently:

SaldoAcumulado =
VAR MaxFecha =
    MAX ( Fecha[Date] ) + TIME ( MAX ( Horas[Value] ), 59, 59 )

RETURN
CALCULATE (
    SUM ( 'log'[Monto] ),
    KEEPFILTERS ( 'log'[Fecha Mov.] <= MaxFecha )
)

This measure first determines the maximum selected date and hour and then calculates the sum while keeping all slicer-applied filters intact. Since your table includes transactions with timestamps down to seconds, ensuring that the Hora Mov. field is appropriately sorted will help maintain accuracy in the cumulative calculation.

The slicers in your report, as seen in the screenshot, filter transactions by date, hour range, and POS ID. The table visual then displays POS, Fecha Mov., Hora Mov., and Monto. The cumulative sum should be computed dynamically based on these slicer selections, ensuring that it only sums the filtered transactions.

To further improve performance, consider:

1. Indexing the log table in your data source if using SQL.
2. Pre-aggregating sums by Date & ID in Power Query before loading into Power BI.
3. Reducing cardinality in Horas[Value] and Fecha[Date] columns to improve query efficiency.

Try this approach, and let me know how it performs in your report!

Best regards,