Answered by:
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. Drilldown showed quite unexpected behavior of SumX formula:
Calculated Field DAX Formula
Result (allup, 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
Answers

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 CALCULATEso 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,
gerhardGerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com Proposed as answer by Darren GosbellMVP Monday, April 14, 2014 12:53 PM
 Marked as answer by Taylor N ClarkMicrosoft employee Wednesday, April 16, 2014 7:57 PM
Monday, April 14, 2014 8:05 AMAnswerer
All replies

I think you need RELATED function for this. using RELATED function
X2:=SUMX(RELATED(Dim_Tenant[TenantID]),SUM(Fact_Tenant[Weight]))
Please Mark This As Answer if it helps to solve the issue Visakh  http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
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).
Visakh, thanks again  your answer helped finding the right formula!
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 CALCULATEso 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,
gerhardGerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com Proposed as answer by Darren GosbellMVP Monday, April 14, 2014 12:53 PM
 Marked as answer by Taylor N ClarkMicrosoft employee Wednesday, April 16, 2014 7:57 PM
Monday, April 14, 2014 8:05 AMAnswerer 
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) )
http://darren.gosbell.com  please mark correct answers
Monday, April 14, 2014 12:53 PM