Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sum with condition

Hi all I made this measure   Mov_cond_PK = sumx(values(Tabella[Product Number]),if([Initial_PK_2]>0,sum(Tabella[moving_PK]),0))   If  I put this measure in a matrix (see below), the figures per...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    See if this works:

     

    Mov_cond_PK =
SUMX (
    VALUES ( Tabella[Product Number] ),
    IF ( [Initial_PK_2] > 0, CALCULATE ( SUM ( Tabella[moving_PK] ) ), 0 )
)

     

    Without the CALCULATE, there is no context transition performed, which means the sum of [moving_PK] is done over all the product numbers in the current filter context rather than just the product number from the row context of the SUMX iterator.

     

    If you define SUM ( Tabella[moving_PK] as a measure SumMovingPK, then you don't have to worry about including the extra CALCULATE (since it's included implicitly) and you can write

     

    Mov_cond_PK =
SUMX (
    VALUES ( Tabella[Product Number] ),
    IF ( [Initial_PK_2] > 0, [SumMovingPK] ) ), 0 )
)

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

See if this works:

 

Mov_cond_PK =
SUMX (
    VALUES ( Tabella[Product Number] ),
    IF ( [Initial_PK_2] > 0, CALCULATE ( SUM ( Tabella[moving_PK] ) ), 0 )
)

 

Without the CALCULATE, there is no context transition performed, which means the sum of [moving_PK] is done over all the product numbers in the current filter context rather than just the product number from the row context of the SUMX iterator.

 

If you define SUM ( Tabella[moving_PK] as a measure SumMovingPK, then you don't have to worry about including the extra CALCULATE (since it's included implicitly) and you can write

 

Mov_cond_PK =
SUMX (
    VALUES ( Tabella[Product Number] ),
    IF ( [Initial_PK_2] > 0, [SumMovingPK] ) ), 0 )
)