Forum Discussion
sum with condition
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 ) )
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)...
Anonymous
Ok, It's not the lowest granularity of your table. You need context transition.
Did you try AlexisOlson proposal?