locked
SumX - Unexpected results RRS feed

  • 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

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

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 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
    Answerer
  • 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