How to apply a measure calculation before roll up

[2366]

Hello,

I needed some help to understand how to apply a DAX expression before roll up -

Here's a sample dataset -

DIM

FACT

I am trying to calculate the total sales amount from every city where the sales rep margin <20%. DAX works fine when I keep Sales Rep in the context but when I remove Sales rep field, the calculation doesnt work anymore

Here's my DAX

Margin = (Sum('Fact'[Selling])-sum('Fact'[Cost]))/sum('Fact'[Cost])

Less 20% Margin = IF([Margin]<.2,sum('Fact'[Selling]),0)

Appreciate any help with this.

Thanks

[AlexisOlson · Accepted solution]

Add an iterator at whatever the appropriate level of granularity is.

Assuming Sales Rep the granularity you want, try this:

Less 20% Margin =
SUMX (
    VALUES ( 'dim'[Sales Rep] ),
    IF (
        [Margin] < .2,
        CALCULATE ( SUM ( 'Fact'[Selling] ) ),
        0
    )
)

[AlexisOlson · Accepted solution]

Add an iterator at whatever the appropriate level of granularity is.

Assuming Sales Rep the granularity you want, try this:

Less 20% Margin =
SUMX (
    VALUES ( 'dim'[Sales Rep] ),
    IF (
        [Margin] < .2,
        CALCULATE ( SUM ( 'Fact'[Selling] ) ),
        0
    )
)

[2366]

Thank you that works!!

I further changed the DAX to calculate margins of the sales reps <.2 and it works however the sub total row aggregates the percentages. Is there anyway to handle this please?

Less 20% Margin 2 =

SUMX(

VALUES ( 'dim'[Sales Rep] ),

IF (

[Margin] < .2,

CALCULATE ( [Margin]) ,

0

)

)

Thanks

[AlexisOlson · Accepted solution]

This measure is designed to add up the values for each Sales Rep separately so it shouldn't be used when you don't want to do that.

I'm not sure, but I'm guessing you want something more like this:

Less 20% Margin 2 =
CALCULATE (
    [Margin],
    FILTER (
        VALUES ( 'dim'[Sales Rep] ),
        [Margin] < .2
    )
)

[ValtteriN]

Hi,

Without the sales rep context your calculation is done to all of the data in the data set and it seems for new york this aggregate is greater than 20%. To solve this you can calculate the margin in a calcuated column and then appy the if condition to the calculated column to get the margin on a transaction level.

I hope this helps to solve your issue and if it does consider accepting this as a solution.