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.
Try using ADDCOLUMNS,
Metric_N =
VAR AggTable =
ADDCOLUMNS (
SUMMARIZE (
'Fact Table',
'Fact Table'[Month Year],
'Fact Table'[Local Currency]
),
"Raw_Metric", CALCULATE ( SUM ( 'Fact_Table'[Raw_Metric] ) )
)
RETURN
SUMX (
AggTable,
VAR fxRate = [User_Selected_Factor] RETURN DIVIDE ( [Raw_Metric], fxRate )
)
SUMMARIZE is great for grouping, but don't use it to add calculated columns. Wrapping ADDCOLUMNS around SUMMARIZE to create the calculated columns is both more efficient and more accurate. The only thing to bear in mind is that when using ADDCOLUMNS you need to trigger context transition, so you need to wrap the code for the column in CALCULATE unless the code is just a measure reference.
I need to calculate the Price, Volume, and Mix effects at the row level, but the effects do not depend on a single dimension — they must be combined with other dimensions as well.
I tried using SUMX with VALUES on a single dimension, and it works only when I slice the data using that exact same dimension. However, as soon as I introduce other dimensions, the calculation no longer returns the expected results.
Hi akim_no ,
Thanks for the update.
If the Price, Volume, and Mix effects rely on combinations of multiple dimensions, then the temporary table used inside SUMX needs to reflect that full grain.
As you've observed, using VALUES on a single column or grouping by fewer columns will cause inconsistencies when other fields are added to the visual.
You can continue using the same ADDCOLUMNS over SUMMARIZE structure as suggested earlier by johnt75 , but extend it to include all relevant dimensions needed for the row-level context. For example:
Metric_N = VAR AggTable = ADDCOLUMNS ( SUMMARIZE ( 'Fact Table', 'Fact Table'[Month Year], 'Fact Table'[Product], 'Fact Table'[Customer], 'Fact Table'[Local Currency] ), "Raw_Metric", CALCULATE ( SUM ( 'Fact Table'[Raw_Metric] ) ), "Units", CALCULATE ( SUM ( 'Fact Table'[Units] ) ) ) RETURN SUMX ( AggTable, VAR fxRate = [User_Selected_Factor] VAR ConvertedMetric = DIVIDE ( [Raw_Metric], fxRate ) RETURN ConvertedMetric )Similar threads for reference:
Solved: SUMX Help with Price part of Price Volume Mix anal... - Microsoft Fabric Community
Solved: Dax for price volume mix effect calculation - Microsoft Fabric Community
Solved: Price Volume Mix analysis - Subtotals & Totals don... - Microsoft Fabric Community
If you're able to share sample PBIX to reproduce the issue, we can help validate the pattern more precisely.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Hope this helps. Please reach out for further assistance.
Thank you.
Hi akim_no ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out. Consider sharing sample data or sample .pbix to guide more accurately.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Thank you.