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.

     

     

johnt75's avatar
johnt75
Super User
1 year ago

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
    Result
akim_no's avatar
akim_no
Helper III
1 year ago

It doesn't allow for correct sales conversion.

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You could include in the temporary table any columns which are needed to drive the conversion. e.g. if you need a currency code column you could use

    Metric_N =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Fact Table',
            'Fact Table'[Currency Code],
            '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
    Result
    • akim_no's avatar
      akim_no
      Helper III
      1 year ago

       

      I actually used a different approach:

       

       Metric_N VAR AggTable =

      SUMMARIZE( 'Fact Table', 'Fact Table'[Month Year], 'Fact Table'[Local Currency], "Raw_Metric", SUM('Fact_Table'[Raw_Metric]) )

      RETURN SUMX(

                 AggTable,

                               VAR fxRate =[User_Selected_Factor]

                                 RETURN DIVIDE([Raw_Metric], fxRate) ) 

       

      This method works, but I’m noticing lower performance when using it for effect calculations (e.g., price/volume/mix)

       

       

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        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.