Forum Discussion
Price / Volume / Mix Effects Calculated at Row Level – Performance Optimization Help Needed
I'm working on a Price / Volume / Mix variance analysis where all effect calculations must be done at the most granular level (row by row), and then aggregated properly for totals in visuals (i.e. no recalculation on subtotal level).
At the same time, I’m facing performance issues, especially with high volume (~2 million rows).
Existing measures:
Current Metric
Metric_N =
SUMX (
'Fact_Table',
DIVIDE('Fact_Table'[Raw_Metric], [User_Selected_Factor])
)Previous Year Metric
Metric_PY =
CALCULATE(
[Metric_N],
SAMEPERIODLASTYEAR('Calendar'[Date])
)Average Index
Avg_Index_N = DIVIDE([Metric_N], [Units_N])
Avg_Index_PY = DIVIDE([Metric_PY], [Units_PY])Price Effect
Effect_Index =
VAR current = [Avg_Index_N]
VAR previous = [Avg_Index_PY]
VAR base_units = [Units_PY]
RETURN
IF(
NOT ISBLANK(current) && NOT ISBLANK(previous),
(current - previous) * base_units,
0
)
Volume Effect
Effect_Volume =
VAR current_units = [Units_N]
VAR previous_units = [Units_PY]
VAR base_index = [Avg_Index_PY]
RETURN
IF(
NOT ISBLANK(base_index),
(current_units - previous_units) * base_index,
0
)
Mix Effect
Effect_Mix =
VAR current = [Avg_Index_N]
VAR previous = [Avg_Index_PY]
VAR current_units = [Units_N]
VAR previous_units = [Units_PY]
RETURN
IF(
NOT ISBLANK(current) && NOT ISBLANK(previous),
(current - previous) * (current_units - previous_units),
0
)
What I’ve tried so far:
I attempted to pre-aggregate using SUMMARIZE at the Month-Year level for Metric_N, but this caused nested measure iterations which negated the performance gains.
- Since effects must be computed per row, I’m unable to simplify the calculations via subtotal-level aggregations
Looking for:
How can I optimize row-level calculations without breaking the logic?
Any best practices or architectural advice for models requiring heavy row-by-row logic?
Thanks in advance for your help! 🙏
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.
16 Replies
Does [User_Selected_Factor] vary on a row by row basis, or can that be computed outside the SUMX in [Metric_N] and stored in a variable? e.g.
Metric_N = VAR UserSelectedFactor = [User_Selected_Factor] RETURN SUMX ( 'Fact_Table', DIVIDE ( 'Fact_Table'[Raw_Metric], UserSelectedFactor ) )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 ?
Hi akim_no ,
Thanks for reaching out to Microsoft Fabric Community.
Just checking in to see if you had a chance to try the suggestion of using ADDCOLUMNS over SUMMARIZE as mentioned by johnt75 .
Using ADDCOLUMNS when defining calculated columns such as Raw_Metric can help improve both performance and accuracy. This approach ensures proper context transition when creating temporary aggregation tables for your measure logic.
Thanks to johnt75 for the helpful guidance.
Let us know if that helped or if you need any further assistance.
Thank you.
I only used Summarize to do the conversion instead. It does the job, but the ADDCOLUMNS gives me results that are very different from what I expect, so I didn’t use it.
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.
