Optimization of DAX SUMMARIZE() with Measure Multiplication

[JordanWelb]

Hi, 

I'm working on optimizing a DAX measure that calculates Total Weighted Bias using [Bias Percent] * [Item Weight]. The calculation is done at a weekly level, grouped by DC, Item, and Week End Date. The source table (Forecast Accuracy) contains over 50 million rows.

I've tried several approaches to improve performance:

• Calculated tables: These are static and don't retain row or filter context, so they don't work dynamically with slicers or visuals.
• SUMMARIZECOLUMNS: This improves performance, but the results are inaccurate compared to the original measure. The bias percentages are off when compared week by week.
• SUMMARIZE + ADDCOLUMNS: This matches the original logic but doesn't improve performance when tested in DAX Studio.
• Virtual tables with SUMX: I’ve built virtual tables that group by the required dimensions and apply the multiplication logic. This gives correct results but performs similarly to the original.

The optimized version using SUMMARIZECOLUMNS seems to calculate bias per week, but when multiple weeks are selected, the numbers don’t match what I get when selecting each week individually. This inconsistency is a major issue.

What Im hoping for is that it calculates the bias across the selected dates and also matches the original logic. 

Has anyone successfully optimized a similar DAX measure on large datasets with multiplication within grouping? Is there a better way to structure the virtual table or use SUMMARIZECOLUMNS in this measure?

The Bias Percent and Item Weight are simple measures. The Store orders and Forecasted Orders are summed up measures. Please dont mind the errors in these measures I changed the names.

This is the Total Weighted Bias measure I need help with: FYI the Fisck Wk and Brand Tables are Dimension tables.

This is the Server Timings in DAX Studio:

I can provide more details if needed. Thank you!

[v-echaithra]

Hi @JordanWelb ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @JordanWelb ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @JordanWelb ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[FBergamaschi · Accepted solution]

You have a hidden call to ALLSELECTED in an iteration so results are impossibile to understand as they are too complex

Remove the internal allselected call and apply allselected only in a final measure that wraps the other measures and/or use variables before enetering the SUMX call to avoid calling allselected inside the iteration

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Greg_Deckler]

@JordanWelb Would need sample data or a sample file to really provide any useful input. You could also try GROUPBY. There do seem to be consistent bugs with SUMMARIZECOLUMNS, it was never originally intended to be used in measures and it shows.

[AlexisOlson]

This might be an issue with the complex behavior of ALLSELECTED. The SQLBI folks recommend, "never create a measure that uses ALLSELECTED within an iteration, and minimize the use of measures that contain ALLSELECTED as much as possible".

@JordanWelb, it looks like [Bias Percent] * [Item Weight] might be able to be simplified by removing the [Store Orders] that cancel each other out:

[Bias Percent] * [Item Weight]
    = DIVIDE ( [Forecasted Orders], [Store Orders] ) * DIVIDE ( [Store Orders], Item_weight )
    = DIVIDE ( [Forecasted Orders], Item_weight )