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 product number are correct (red circle) but the sum at period num level is not correct (highlighted in yellow).   In the example below, it should be 5.417,49 instead of 25.653,11

 

 

What could it be due to?

How can I change the formula? Thanks

 

 

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

     

9 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Try:

    Mov_cond_PK =

    sumx(values(Tabella[Product Number]),if([Initial_PK_2]>0,Tabella[moving_PK],0))

    I removed the SUM wrapping the Tabella[moving_PK] column

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the reply.

      it doesn't work, I think it expects a measure there

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        That is strange becasue SUMX creates a Row Context so you don't have to put a measure there. 
        The classic utlization of it is actually with a column or calculation of columns from that row. if you put a measure it will create context transition (you need that sometimes) but you have put a direct sum fucntion that usually is the reason for mistakes when using iterators.
        BTW, what is the measure [Initial_PK_2]?

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