Forum Discussion
Helper IIOptimizing ABC Analysis in Power BI with Large Sales Data – Exceeding Resources Issue
This type of run time calculation on large data will always be slow. I'm not surprised to hear of your issues. Anything you can do to remove unnecessary resource theft will help. I suggest remove the product description from the fact table and put it in a product table, ideally with an integer as the key for the relationship. Also, check the precision of the sales value column. Reducing precision should help, eg round to the nearest integer. Consider summarising the data across dimensions that don't matter for this calculation, eg if your data is at day level of granularity but you only do these calculations at a month level, consider creating a summarised table of data at the month level only for this calculation.
Community ChampionThis type of run time calculation on large data will always be slow. I'm not surprised to hear of your issues. Anything you can do to remove unnecessary resource theft will help. I suggest remove the product description from the fact table and put it in a product table, ideally with an integer as the key for the relationship. Also, check the precision of the sales value column. Reducing precision should help, eg round to the nearest integer. Consider summarising the data across dimensions that don't matter for this calculation, eg if your data is at day level of granularity but you only do these calculations at a month level, consider creating a summarised table of data at the month level only for this calculation.
Helper IIThanks 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?