cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonV Frequent Visitor
Frequent Visitor

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:

 

column-measure.PNG

 

 

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?
7 REPLIES 7
Community Support Team
Community Support Team

Re: Add difference between column and measure at value

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JonV Frequent Visitor
Frequent Visitor

Re: Add difference between column and measure at value

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.

Community Support Team
Community Support Team

Re: Add difference between column and measure at value

Hi @JonV,

 

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

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JonV Frequent Visitor
Frequent Visitor

Re: Add difference between column and measure at value

I'd love to. But I don't see an option to do so. Do I need to upload it elsewhere?

Community Support Team
Community Support Team

Re: Add difference between column and measure at value

Hi @JonV,

 

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JonV Frequent Visitor
Frequent Visitor

Re: Add difference between column and measure at value

I've uploaded a simplified file to my Google drive. Please let me know if it works.

 

https://drive.google.com/open?id=1GCNsAYE4WStfd8pocGcPapeAxblyGQf3

Community Support Team
Community Support Team

Re: Add difference between column and measure at value

Hi @JonV,

 

You just need to remove the filter context. Please try the formula below.

Adjusted Frequency =
IF (
    MIN ( 'Test Frequency'[Quantity] ) < 20,
    SUMX (
        'Test Frequency',
        IF (
            'Test Frequency'[Quantity] < 20,
            'Test Frequency'[Frequency] * [Adjustment]
        )
    ),
    CALCULATE (
        SUM ( 'Test Frequency'[Frequency] )
            - SUMX (
                'Test Frequency',
                IF (
                    'Test Frequency'[Quantity] < 20,
                    'Test Frequency'[Frequency] * [Adjustment]
                )
            ),
        ALL ( 'Test Frequency' )
    )
)

Add-difference-between-column-and-measure-at-value

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.