Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Let's see if I've understood correctly. Try this:
Measure = SUMX ( DFC, DFC[SpreadUnit] * IF ( DFC[Client] = "X", ( 1 - 0.20 ), 1 ) )
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.
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?
Let's see if I've understood correctly. Try this:
Measure = SUMX ( DFC, DFC[SpreadUnit] * IF ( DFC[Client] = "X", ( 1 - 0.20 ), 1 ) )
Or an alternative:
Measure_v2 = SUMX ( DFC, IF ( DFC[Client] = "X", DFC[SpreadUnit] * ( 1 - 0.20 ), DFC[SpreadUnit] ) )
and yet another version:
Measure_v3 = CALCULATE ( SUM ( DFC[SpreadUnit] ), DFC[Client] = "X" ) * ( 1 - 0.20 ) + CALCULATE ( SUM ( DFC[SpreadUnit] ), DFC[Client] <> "X" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |