Optimizing ABC Analysis in Power BI with Large Sales Data – Exceeding Resources Issue

Thank you for the suggestion! I really appreciate the insights. I’ll definitely try creating a summarized table at the month level since we typically view this indicator at a monthly minimum. This should hopefully reduce the strain on resources and allow for smoother calculations. Thanks again for pointing me in the right direction!

Thanks for your suggestions!

To optimize performance, I already aggregated my sales data in Power Query before loading it into Power BI. My transformation removes unnecessary columns, filters only "Normal" sales, and groups data at the store, product, and month level. Here’s the Power Query code I’m using:

let
    Source = fVendas,

    // 1. Filtering records before any transformation
    FilterNormal = Table.SelectRows(Source, each [situacao] = "Normal"),

    // 2. Selecting only the necessary columns before processing the data
    SelectColumns = Table.SelectColumns(FilterNormal, {"DATA_EMISS", "lkpdv", "LKEMPRESA", "LKPRODUTO", "TotalItem", "QUANTIDADE"}),

    // 3. Creating Month and Year columns (keeping DATA_EMISS as a date)
    AddMonth = Table.AddColumn(SelectColumns, "Month", each Date.Month([DATA_EMISS]), Int64.Type),
    AddYear = Table.AddColumn(AddMonth, "Year", each Date.Year([DATA_EMISS]), Int64.Type),

    // 4. Creating a Reference Date column (first day of the month)
    AddReferenceDate = Table.AddColumn(AddYear, "ReferenceDate", each #date([Year], [Month], 1), type date),

    // 5. Grouping data to reduce volume before further operations, including QUANTIDADE (Quantity)
    GroupedData = Table.Group(AddReferenceDate, {"Year", "Month", "ReferenceDate", "lkpdv", "LKEMPRESA", "LKPRODUTO"}, 
        {{"TotalValue", each List.Sum([TotalItem]), type number},
         {"TotalQuantity", each List.Sum([QUANTIDADE]), type number}}),
    
    // 6. Changing data type for better usability
    #"Changed Type" = Table.TransformColumnTypes(GroupedData,{{"TotalValue", Currency.Type}})
in
    #"Changed Type"

Even after summarizing the data, I still run into the "Resources Exceeded" error when calculating cumulative sales for the Pareto percentage in my ABC analysis.

Given that my sales table has over 10 million rows and the product table contains more than 20,000 products, do you have any additional suggestions?

Would a different DAX approach or another pre-aggregation step help optimize cumulative calculations at scale?