locked
SUMX with conditional rolling up not as intended RRS feed

  • Question

  • Hello,

    My data table is a list of sales by order number, region, and quarter. In a previous post, I received help to create a count for order number that would bifurcate the count if a single order spanned across multiple sites: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bd88d3e2-93f8-4418-85d1-428ffb776b29/split-distinct-count-into-fractions-of-a-count?forum=sqlkjpowerpivotforexcel

    I'm now trying to modify that formula to find rates of $/qty above a certain threshold and sum the qty where that's true. I've created a simple column in my dataset for the threshold and created a simple formula for the rate calculation: 

    =DIVIDE(SUM(Orders[Revenue]),sum(Orders[QTY]))


    Then my formula to evaluate the rate against my threshold is as follows:

    =SUMX(SUMMARIZE(Orders,Orders[OrderNumber]),if([Rate]>=AVERAGE(Orders[BaslineRate]),sum(Orders[QTY]),0))


    This formula works great when my pivot is showing each order number, but blows up on the grand total, so I added a divide by the row count:

    =DIVIDE(SUMX(SUMMARIZE(Orders,Orders[OrderNumber]),if([Rate]>=AVERAGE(Orders[BaslineRate]),sum(Orders[QTY]),0)),SUMx(SUMMARIZE(Orders,Orders[OrderNumber]),if([Rate]>=AVERAGE(Orders[BaslineRate]),1,0)))

    The result above almost works, however my total is now equal to the total of Qty as a whole because the total rate happens to be above the threshold. So it's appropriately evaluating the grand total as is, but I need it instead to be the sum of QTY that meets the criteria. For example, my dataset when pivoted by ordernumber comes up to 103 rows. 16 of those meet the criteria of being over the BaslineRate. Each of those 16 appropriately displays the QTY for those order numbers and the rest are 0. however the Total is equal to the Total QTY, not the total of just the 16 orders.



    - Addison

    Tuesday, November 28, 2017 6:36 PM

Answers

  • This can get quite detailed and if you read any books on DAX they often have an entire chapter devoted to how CALCULATE works. But in a nutshell this works because DAX has 2 types of contexts within which expressions are evaluated - Row Context and Filter Context. And CALCULATE (and CALCULATETABLE ) do 2 things:

    1. they let you alter the filter context by applying additional filters or by removing filters (using ALL() ) 
    2. and they transition Row contexts to Filter contexts

    Row contexts only exist in calculated columns and inside iterator functions like SUMX, COUNTX, AVERAGEX.

    Your issue occurred because aggregate functions (like SUM, COUNT etc) are evaluated using the filter context. So while the outer SUMX was iterating over each distinct OrderNumber, the OrderNumber was in the row context and was not being applied to the SUM - adding the call to CALCULATE fixed this.

    Note that all measures are wrapped in an implied CALCULATE by the DAX engine. so if you had a measure like:

    [Total Qty] := SUM(Order[Qty])

    And then used this in your expression:

    =SUMX(
       SUMMARIZE(Orders,Orders[OrderNumber])
       ,if([Rate]>=AVERAGE(Orders[BaslineRate]),[Total Qty]),0))

    it would have worked fine the first time due to the implied calculate.

    You can read more about context transitions here https://www.sqlbi.com/articles/understanding-context-transition/


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 29, 2017 12:10 AM

All replies

  • So your issue here is quite subtle, but not difficult to fix. The problem is the code in bold below.

    =SUMX(
       SUMMARIZE(Orders,Orders[OrderNumber])
       ,if([Rate]>=AVERAGE(Orders[BaslineRate]),sum(Orders[QTY]),0))

    At the grand total level this evaluates to the total count of all orders. So your expression actually says that for each order that is over the average baseline add the grand total so if you had 1000 orders and 200 were over the baseline then you would get a total from this calculation equal to 1000 x 200. 

    To fix this we just need to apply the row context from the SUMX to the bolded code. You can either do this by creating this as a measure and referencing that measure in your expression or by wrapping it in a calculate().

    eg

    =SUMX(
       SUMMARIZE(Orders,Orders[OrderNumber])
       ,if([Rate]>=AVERAGE(Orders[BaslineRate]),CALCULATE(sum(Orders[QTY])),0))


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 28, 2017 10:23 PM
  • Whaaaaat! Wow. First off, thank you! I was a little worried I was too convoluted. Second, why does this work? I thought the only benefit/use of CALCULATE was with corresponding filter expressions?

    - Addison

    Tuesday, November 28, 2017 11:09 PM
  • This can get quite detailed and if you read any books on DAX they often have an entire chapter devoted to how CALCULATE works. But in a nutshell this works because DAX has 2 types of contexts within which expressions are evaluated - Row Context and Filter Context. And CALCULATE (and CALCULATETABLE ) do 2 things:

    1. they let you alter the filter context by applying additional filters or by removing filters (using ALL() ) 
    2. and they transition Row contexts to Filter contexts

    Row contexts only exist in calculated columns and inside iterator functions like SUMX, COUNTX, AVERAGEX.

    Your issue occurred because aggregate functions (like SUM, COUNT etc) are evaluated using the filter context. So while the outer SUMX was iterating over each distinct OrderNumber, the OrderNumber was in the row context and was not being applied to the SUM - adding the call to CALCULATE fixed this.

    Note that all measures are wrapped in an implied CALCULATE by the DAX engine. so if you had a measure like:

    [Total Qty] := SUM(Order[Qty])

    And then used this in your expression:

    =SUMX(
       SUMMARIZE(Orders,Orders[OrderNumber])
       ,if([Rate]>=AVERAGE(Orders[BaslineRate]),[Total Qty]),0))

    it would have worked fine the first time due to the implied calculate.

    You can read more about context transitions here https://www.sqlbi.com/articles/understanding-context-transition/


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 29, 2017 12:10 AM
  • Hi Madadd33,

    Any update? I am marking answer of Darren.If you have any concern,Let me know.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, January 18, 2018 11:35 AM
    Answerer