SUMX at an aggregated level?

[Anonymous]

I have some data that looks like this:

Month	Parent	Child	Cupcakes
Feb	Smith	1	10
Feb	Smith	2	5
Feb	John	1	7
Jul	Smith	1	10

I have several measures:

• Sum of Feb cupcakes
• Sum of Jul cupcakes
• Jul - Feb (change)
• Change = if(jul-feb<0,"Decliner","Advancer")

The goal is to sum all of the advancers at the parent level. I used a sumx but it's determining if it's advancer/decliner at the child level... any idea on how to establish if it's an advacner/decliner at the parent level? Thanks so much!! 

[tamerj1]

Hi @Anonymous 
Please try

Average =
SUMX ( VALUES ( 'Table'[Parent] ), IF ( [Change] = "Advancer", 1 ) )

[Anonymous]

I think this sums the number of parents. I'm looking to sum cupcakes at the parent level (versus child level). Thanks so much! 

[tamerj1]

 @Anonymous 

Please try

Average =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Parent], 'Table'[Child] ),
    IF ( [Change] = "Advancer", 1 )
)

[Anonymous]

I'm trying to sum the measure Jul minus Feb at the parent level. How do I incorporate that into the formula? Thanks so much! 

Parent	Jul-Feb (Change in Cupcakes)	Decliner/Advancer
Smith	-5	Decliner
John	-7	Decliner

But then the goal of this new sumx to sum all of the decliners (-12)

[tamerj1 · Accepted solution]

@Anonymous 

Like this

Average =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Parent], 'Table'[Child] ),
    IF ( [Change] = "Decliner", [Jul-Feb] )
)