Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

 

mbighi_0-1650635062073.png

 

What could it be due to?

How can I change the formula? Thanks

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
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 )
)

 

View solution in original post

9 REPLIES 9
AlexisOlson
Super User
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 )
)

 

SpartaBI
Community Champion
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

Thanks for the reply.

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

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

 

mbighi_0-1650637740873.png

The comparison will be a ratio between the 2 measures.

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

 

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

 

 

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

Anonymous
Not applicable

@SpartaBI ,

@AlexisOlson 's proposal works perfectly!

Thanks guys, your help was really appreciated!!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.