sum with condition

[Anonymous]

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

[AlexisOlson · Accepted solution]

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

[SpartaBI]

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]

Thanks for the reply.

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

[SpartaBI]

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]

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]

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

[Anonymous]

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

[SpartaBI]

@Anonymous 
Ok, It's not the lowest granularity of your table. You need context transition.
Did you try @AlexisOlson proposal?

[Anonymous]

@SpartaBI ,

@AlexisOlson 's proposal works perfectly!

Thanks guys, your help was really appreciated!!