I'm a relative neophyte with DAX so bear with me. In the simplest terms, I want to double the measure amount for all regions that are not Europe, then sum the result. Here is some example DAX:
DEFINE
measure Fact[test] = CALCULATE (IF(SELECTEDVALUE('Dim'[Region]) = "Europe", Fact[Revenue],Fact[Revenue] * 2))
EVALUATE(
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('Dim'[Region], "RegionSubtotal"),
"Original Measure", Fact[Revenue],
"New Measure", Fact[test]
)
)
Region | RegionSubtotal | Original Measure | New Measure |
---|---|---|---|
Asia | False | 200 | 400 |
Americas | False | 500 | 1000 |
Europe | False | 300 | 300 |
True | 1000 | 2000 |
I'm trying to get (400+1000+300) = 1700 for the second column instead of 2000. Any ideas?
For the subtotal row, the selected value is not "Europe" so it's doubling the value.
To fix this, you want to iterate over the regions in your measure. Something like this:
test =
SUMX (
VALUES ( 'Dim'[Region] ),
IF (
'Dim'[Region] = "Europe",
[Revenue],
[Revenue] * 2
)
)