Forum Discussion
Price / Volume / Mix Effects Calculated at Row Level – Performance Optimization Help Needed
Hi akim_no ,
Thanks for sharing the update.
Noted that you're currently using SUMMARIZE for the conversion and it seems to be doing the job in your scenario. That’s good to hear.
If ADDCOLUMNS is returning unexpected results, it’s worth double-checking two things:
Ensure any calculated column inside ADDCOLUMNS (like Raw_Metric) is wrapped in CALCULATE, for example:
"Raw_Metric", CALCULATE(SUM('Fact Table'[Raw_Metric]))
This helps maintain the correct context for aggregation within each group.Also, check that the table you're passing to ADDCOLUMNS includes all the necessary columns to define the grouping. If any key column is missing, the results might not match due to changes in context.
While SUMMARIZE can work in many scenarios, ADDCOLUMNS tends to preserve row context better, especially when dealing with calculations that depend on multiple dimensions or filter propagation.
If you're still seeing performance issues or unexpected results, feel free to share a sample PBIX or a few rows of sample data.
Hope this helps. Please reach out for further assistance.
Thank you.
Yes, [User_Selected_Factor] changes at the row level, so I intentionally avoided storing it in a variable to ensure it gets recalculated per row during evaluation.
I don't understand what went wrong when you tried with an aggregate table. If you store the values of Metric N in an aggregate table, would the new definition not be
Metric_N =
SUM ( 'Summary Table'[Metric N] )
And then all other measures would stay the same?
Also, what is the definition of [Units_N] ? Can you pre-aggregate that as well ?
"[Metric_N]" represents my revenue converted to USD, using a conversion rate dynamically selected by the user (User_Selected_Factor).
I then calculate the other indicators based on this converted value."Units_N" corresponds to the quantity sold, and it is also pre-aggregated upstream to improve performance.
However, despite these optimizations, performance remains poor.
I’m required to calculate Price, Volume, and Mix effects at the most granular level (row by row).
Even though the indicators themselves remain the same, the calculation logic must change:
instead of computing effects from aggregated totals, I now need to compute them for each row individually and then aggregate the results.The need to compute all effect measures row by row means performance is even worse
You could try and minimise the number of rows you need to iterate by creating a temporary table with the distinct values from the revenue and the number of occurences, e.g.
Metric_N = VAR SummaryTable = ADDCOLUMNS ( VALUES ( 'Fact Table'[Raw Metric] ), "@num rows", CALCULATE ( COUNTROWS ( 'Fact Table' ) ) ) VAR Result = SUMX ( SummaryTable, DIVIDE ( 'Fact Table'[Raw Metric] * [@num rows], [User_Selected_Factor] ) ) RETURN ResultIt doesn't allow for correct sales conversion.