topic Re: SUMX at an aggregated level? in DAX Commands and Tips

SUMX at an aggregated level?

Anonymous — Thu, 28 Jul 2022 18:31:55 GMT

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

Re: SUMX at an aggregated level?

tamerj1 — Thu, 28 Jul 2022 19:11:48 GMT

Hi Anonymous
Please try

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

Re: SUMX at an aggregated level?

Anonymous — Thu, 28 Jul 2022 19:16:40 GMT

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

Re: SUMX at an aggregated level?

tamerj1 — Thu, 28 Jul 2022 19:18:55 GMT

Anonymous

Please try

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

Re: SUMX at an aggregated level?

Anonymous — Thu, 28 Jul 2022 19:52:13 GMT

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)

Re: SUMX at an aggregated level?

tamerj1 — Thu, 28 Jul 2022 20:04:52 GMT

Anonymous

Like this

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