Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JonV
Helper II
Helper II

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
v-jiascu-msft
Employee
Employee

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.

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.

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.

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

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.

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

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.