topic Re: How to apply a measure calculation before roll up in DAX Commands and Tips

How to apply a measure calculation before roll up

2366 — Wed, 15 Dec 2021 10:24:28 GMT

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

Re: How to apply a measure calculation before roll up

ValtteriN — Wed, 15 Dec 2021 11:16:43 GMT

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.

Re: How to apply a measure calculation before roll up

AlexisOlson — Wed, 15 Dec 2021 16:02:32 GMT

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

Re: How to apply a measure calculation before roll up

2366 — Wed, 15 Dec 2021 17:46:48 GMT

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

)

Thanks

Re: How to apply a measure calculation before roll up

AlexisOlson — Wed, 15 Dec 2021 18:37:57 GMT

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