Forum Discussion

2366's avatar
2366
Icon for Helper I rankHelper I
4 years ago
Solved

How to apply a measure calculation before roll up

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 a...
DAX Calculation
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

    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
    )
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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's avatar
2366
Icon for Helper I rankHelper I
4 years ago

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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
    )
)