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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SUMX at an aggregated level?

I have some data that looks like this:

MonthParentChildCupcakes

Feb

Smith110
FebSmith25
FebJohn17
JulSmith110

 

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

1 ACCEPTED SOLUTION

@Anonymous 

Like this

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

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

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

 @Anonymous 

Please try

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

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! 

 

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

 

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

@Anonymous 

Like this

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors