Subtotal <> sum of the rows

Question

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?

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-31T02:32:24

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

Subtotal <> sum of the rows

2 Answers