Measure for SUM, but discounting some values before

[PietroFarias]

I have a measure that performs the sum of a column.

Spread: = SUM (DFC [SpreadUnit])

Now, I'd like to do a calculation that I'd like to do in a simplified way, different from what I'm currently doing. The calculation would be to perform the sum of the SpreadUnit column, BUT when the Customer column is X, for example, calculate [SpreadUnit] * (1-0,20), so that it discounts 20% of the value. What is the best way to achieve this result?

P.s: I'm using Power Pivot for Excel 2016

[AlB]

Hi @PietroFarias

Can you explain a bit more what you need?

Is it the sum of the SpreadUnit column and multiplying the result by (1-0,20)? 

Is it multiplying each row in SpreadUnit by a discount given in another column (which one?) and then sum all those results? 

Is it the sum of the rows of  SpreadUnit for which a column called Customer has value "X"?

It'd be much easier if you show a sample of the tables in your data model and also an illustrative example based on that data, showing the expected result.

[PietroFarias]

I'll illustrate how I do today. I have one SPREAD column and another is a calculated column called SPREAD_2. To perform the addition of SPREAD simply use SUM ([Spread]), done. However for SPREAD_2 every time the client is X it performs [SPREAD] * (1-0.20), otherwise it returns SPREAD. Then just create a measure to add this SPREAD_2.

But I would like to do this directly in one measure. How would I do it?

[AlB · Accepted solution]

@PietroFarias

Let's see if I've understood correctly. Try this:

Measure =
SUMX ( DFC, DFC[SpreadUnit] * IF ( DFC[Client] = "X", ( 1 - 0.20 ), 1 ) )

[AlB]

@PietroFarias

Or an alternative:

Measure_v2 =
SUMX (
    DFC,
    IF ( DFC[Client] = "X", DFC[SpreadUnit] * ( 1 - 0.20 ), DFC[SpreadUnit] )
)

[AlB]

@PietroFarias

and yet another version:

Measure_v3 =
CALCULATE ( SUM ( DFC[SpreadUnit] ), DFC[Client] = "X" ) * ( 1 - 0.20 )
    + CALCULATE ( SUM ( DFC[SpreadUnit] ), DFC[Client] <> "X" )