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 · Accepted Answer · 2020-12-31T15:30:11.337

2

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

edited Dec 31 '20 at 15:30

answered Dec 31 '20 at 02:32

Alexis Olson

38,724
7
42
64

That does make sense (evaluate each row individually and sum the result) but unfortunately i'm getting the same result as before – justinm1 Dec 31 '20 at 03:07
1

I think the SELECTEDVALUE should be removed, since inside the SUMX we have a row context over 'Dim'[Region] – sergiom Dec 31 '20 at 07:29
Thanks, looks like that did it. – justinm1 Dec 31 '20 at 15:03
Please can you tell me what is the purpose of using VALUES instead of the table name (without VALIES). – variable Jan 01 '21 at 20:21
If there is only one row per `Region` then the table name works fine. I used VALUES in case `'Dim'` has more than one row per `Region`, which would make the calculation less efficient (since it's iterating over more rows) and prone to double-counting in some cases (e.g. if `[Revenue]` modifies the filter context in certain ways). – Alexis Olson Jan 02 '21 at 06:56

score 1 · Answer 2 · answered Dec 31 '20 at 08:18

1

An other alternative would be to create a calculated column wherein , in case if region<>Europe, then amount* 2 else amount. Then take the sum of the calculated column , but this would be like having an additional data .

answered Dec 31 '20 at 08:18

Nandan

3,939
2
8
21

Subtotal <> sum of the rows

2 Answers2

Linked