Forum Discussion

JonV's avatar
JonV
Helper II
7 years ago

Add difference between column and measure at value

Greetings all. I've got a rather difficult challenge. First off, here's what I'm working with:

 

 

 

The first 2 columns come from a static source and are a quantity of a specific item, and how frequently customers purchase that quantity (ex: Customers bought that item in a quantity of 7 nine times). The third column comes from a measure with a bunch of math behind it that's not particularly important. From there, I created a measure that, for quantity 1-19, it multiplies the frequency * adjustment (where the adjustment is always between 0 and 1). The measure I wrote is:

 

Adjusted Frequency = SUMX('Sample', IF('Sample'[Quantity] < 20, 'Sample'[Frequeny] * [Adjustment]))
 
What I'm wanting the function to do is, instead of calculating the same frequency * adjustment at 20, I want it to calculate the difference between the SUM of rows 1-19 for the frequency and adjusted frequency, and instead ADD it to the frequency.
 
So to walk through the above example, the SUM of Frequency from 1-19 = 239. The SUM of Adjusted Frequency from 1-19 = 100.41. The difference is 239 - 100.41 = 138.59, which gets added to the original frequency of 2 on line 20 giving 140.59 for that line. Thus, what I need is a DAX function to calculate that which I can drop in the else section of the IF function.
 
I've been able to get a function to get the SUM of Frequency for 1-19, but because Adjusted Frequency is a measure and not a column, which means I can't use SUM on it so I'm rather stumped on another way to approach it.
 
So is there a way to do a SUM there? Or a different approach to arrive at the solution?
Need Help

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi JonV,

     

    You can try the formula below. Please refer to dax/summarize-function-dax.

     

    measure =
SUMX (
    SUMMARIZE (
        sample,
        [Quantity],
        [Frequency],
        [Adjustment],
        "AF", [Adjusted Frequency]
    ),
    [AF]
)

     

    Best Regards,

    • JonV's avatar
      JonV
      Helper II
      7 years ago

      This solution will not work. As noted in my OP, it needs to be able to be dropped into the IF function as the 3rd term to complete the blank cell. This function references that entire column which creates a circular dependency as it's trying to fill a cell in the column it's trying to calculate.

       

      I should also note that the "Adjustment" column came from a measure which also complicates what DAX functions can reference it.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi JonV,

         

        Can you share the file? A dummy one is enough. Please mask the sensitive parts.

         

        Best Regards,