cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## sum with condition

Hi all

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

1 ACCEPTED SOLUTION
Super User

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 9
Super User

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

Community Champion

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
Not applicable

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

Community Champion

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
Not applicable

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.

Community Champion

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

Anonymous
Not applicable

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

Community Champion

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

Anonymous
Not applicable

@AlexisOlson 's proposal works perfectly!

Thanks guys, your help was really appreciated!!