# SumX - Unexpected results • ### Question

• Hello,

One of my models is using following tables:

 Fact_Tenant Dim_Tenant TenantID Geo Workload AU Weight TenantID Name 1 US EXO 19 0.20212766 1 A 1 Canada EXO 15 0.15957447 2 B 1 Mexico EXO 17 0.18085106 3 C 1 Japan EXO 14 0.14893617 4 D 1 US SPO 0 0 1 Canada SPO 1 0.125 1 Mexico SPO 7 0.875 1 Japan EXO 29 0.30851064 2 US EXO 21 0.375 2 Canada EXO 16 0.28571429 2 Mexico EXO 14 0.25 2 Japan EXO 2 0.03571429 2 US SPO 22 0.53658537 2 Canada SPO 8 0.19512195 2 Mexico SPO 11 0.26829268 2 Japan EXO 3 0.05357143 3 US EXO 10 0.27027027 3 Canada EXO 18 0.48648649 3 Mexico EXO 3 0.08108108 3 Japan EXO 3 0.08108108 3 US SPO 2 0.08333333 3 Canada SPO 1 0.04166667 3 Mexico SPO 21 0.875 3 Japan EXO 3 0.08108108

TenantID is used for linking between fact and dimension table. The calculation based on [Weight] was generating unexpected results. Drill-down showed quite unexpected behavior of SumX formula:

 Calculated Field DAX Formula Result (all-up, no   filtering) X2:=SUMX(VALUES(Dim_Tenant[TenantID]),SUM(Fact_Tenant[Weight])) 24 X2b:=SUMX(VALUES(Fact_Tenant[TenantID]),SUM(Fact_Tenant[Weight])) 18 X2c:=SUM(Fact_Tenant[Weight]) 6

Why is expression in SumX() calculated for entire table? I expected it will be calculated within the context of SumX()’s table expression (i.e. only for each member of VALUES(Dim_Tenant[TenantID]) or VALUES(Fact_Tenant[TenantID])).

Is describe behavior expected?

Thank you for insights and help!

Daniel

Saturday, April 12, 2014 5:52 AM

• Just to clarify on the "why"

1) the total sum of [Weight] is 6 as your [X2c] measure shows correctly
2) There are 4 different values in 'Dim_Tenant' so you see 4 * 6 = 24 for [X2b]
3) There are 3 different Tenants in 'Fact_Tenant' so you see 3 * 6 = 18 for [X2]

SUMX iterates over the values specified in the first parameter
the expression (=second parameter) is then calculated for each iteration
in order to make use of the context of the current iteration (=current tenant) you need to use CALCULATE

so your correct expression would be

X2:=SUMX(VALUES(Dim_Tenant[TenantID]),CALCULATE(SUM(Fact_Tenant[Weight])))

without the CALCULATE the value will not change with each iteration hence you always get 6, depending on the number of iterations you would then sum up 6 n times giving you 18 or respectively 24

a CALCULATE is automatically wrapped around your expression internally if you refer to an other calculated measure as you did with your workaround - and thats the reason why it works

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Monday, April 14, 2014 8:05 AM

### All replies

• I think you need RELATED function for this. using RELATED function

```X2:=SUMX(RELATED(Dim_Tenant[TenantID]),SUM(Fact_Tenant[Weight]))
```

Saturday, April 12, 2014 8:00 AM
• Thank you very much Visakh,

Your solution generates following error: "Measure 'Fact_Tenant'[X2b] : The column 'Dim_Tenant[TenantID]' either doesn't exist or doesn't have a relationship to any table available in the current context.".

The message sounds confusing, because both field and relationship obviously exist (validated by using in pivot table: sum of weights is showed correctly by each TenantID pivoted in rows). The problem is obviously hidden in the "current context" part of the error message. Sum expression obviously impacts the context of the formula so that table expression is ignored.

I've tried work around the issue by keeping the Sum expression separately:

• First, expression "Sum of Weights:=SUM(Fact_Tenant[Weight])" was created
• Then the original expression was altered into "X2:=SUMX(DISTINCT(Dim_Tenant[TenantID]),[Sum of Weights])"

This approach works correctly (also I have to admit I do not fully understand why).

Daniel

Sunday, April 13, 2014 7:24 AM
• Just to clarify on the "why"

1) the total sum of [Weight] is 6 as your [X2c] measure shows correctly
2) There are 4 different values in 'Dim_Tenant' so you see 4 * 6 = 24 for [X2b]
3) There are 3 different Tenants in 'Fact_Tenant' so you see 3 * 6 = 18 for [X2]

SUMX iterates over the values specified in the first parameter
the expression (=second parameter) is then calculated for each iteration
in order to make use of the context of the current iteration (=current tenant) you need to use CALCULATE

so your correct expression would be

X2:=SUMX(VALUES(Dim_Tenant[TenantID]),CALCULATE(SUM(Fact_Tenant[Weight])))

without the CALCULATE the value will not change with each iteration hence you always get 6, depending on the number of iterations you would then sum up 6 n times giving you 18 or respectively 24

a CALCULATE is automatically wrapped around your expression internally if you refer to an other calculated measure as you did with your workaround - and thats the reason why it works

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Monday, April 14, 2014 8:05 AM
• Gehard's answer is correct. However it's not clear what you are trying to do with SUMX in the first place.

If you are trying to get a grand total of All tenants the following would be faster and would more clearly show that intention.

=CALCULATE( SUM( Fact_Tenant[Weight]  ), ALL(Dim_Tenant) )