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

     

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]?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Initial_PK_2 =

    var vMinDate = minx(allselected(Calendario),Calendario[date])
    VAR vCalc=
    calculate(
    sum([moving_PK]),

    all('Calendario'),
    'Calendario'[date] = vMinDate,
    all('Tabella'),
    values(Tabella[Product Number])

    )
    RETURN vCalc

     

    I need to compare movingPK of an initial month (Initial PK) (selected to a slicer) with the movingPK of every month (only for the comparable product number, ie product number with Initial PK>0 and moving PK>0).

     

    The comparison will be a ratio between the 2 measures.

    The measure works per product number, but not in the upper level.

     

    • SpartaBI's avatar
      SpartaBI
      Community Champion
      4 years ago

      Can you share the result you are getting with the measure I sent you

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Mov_cond_PK =

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

         

        If I remove the sum I get this error:

        "A single value for column 'moving_PK' in table 'Tabella' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

         

        Furthermore, if I digit your formula, when I arrive at Tabella[moving_PK] pbi suggests me to choose among a list of calculated measures (moving_PK is not)...