Forum Discussion

akim_no's avatar
akim_no
Helper III
1 year ago
Solved

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...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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.

     

     

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
1 year ago

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.

 

 

  • akim_no's avatar
    akim_no
    Helper III
    1 year ago

    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.

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Community Support
      1 year ago

      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.

       

       

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Community Support
        1 year ago

        Hi akim_no ,
        Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.