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

4 Replies

  • 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
    )
)
  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.