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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Captain_Jack909
Frequent Visitor

Sumx on max per each category

Hej

I am struggling with a sumx on the max value per category,  really hope if someone could help me here!

I have a fact table as below which I would like to sum the quantity for each max FCround on the category , meaning sum all the values = 20

Captain_Jack909_0-1678221468778.png

I have tried different measures but still I could not achive it, can anyone help here

 

Measure1 = var MaxFCround_perCategory = CALCULATE(MAX(Forecast[FC round]),ALLEXCEPT(Dim, Dim[Category]))
return CALCULATE(SUM(Forecast[Quantity]),FILTER(Forecast,Forecast[FC round]=MaxFCround_perCategory))
 
Measure2 = SUMX(SUMMARIZE(Forecast,Forecast[Category],"Quantity_maxFCround",CALCULATE(SUM(Forecast[Quantity]),FILTER(Forecast,Forecast[FC round]=MAX(Forecast[FC round])))),[Quantity_maxFCround])
 
Measure3 = var MaxFCround_perCategory =  CALCULATE(MAX(Forecast[FC round]),ALLEXCEPT(Dim, Dim[Category]))
 return
 SUMX(SUMMARIZE(Forecast,Forecast[Category],"Quantity_maxFCround",CALCULATE(SUM(Forecast[Quantity]),FILTER(Forecast,Forecast[FC round]=MaxFCround_perCategory))),[Quantity_maxFCround])

 

Captain_Jack909_1-1678221564364.png

thanks in advance

Jack

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Captain_Jack909 ,

 

Here are the steps you can follow:

1. Create measure.

Measure =
MAXX(
    FILTER(ALL('Table'),
   'Table'[Category]=MAX('Table'[Category])&&
    'Table'[FC round]=
    MAXX(
        FILTER(ALL('Table'),
        'Table'[Category]=MAX('Table'[Category])),[FC round])),[Quantity])
Measure2 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=MAX('Table'[Category])&&
    'Table'[FC round]=
    MAXX(
        FILTER(ALL('Table'),
        'Table'[Category]=MAX('Table'[Category])),[FC round])),[Quantity])

2. Result:

vyangliumsft_0-1678436481719.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hello @v-yangliu-msft 
THANKS so much for spending time and trying to help me here ! really appreciate !

I have tried your suggestions as below in measure 1 and measure 2 , however its still not what I would like to achieve, I want to show and sum value per subcategory based on the max FC round from category, 
I can now achieve that on row level , but there are some problems on the aggregated results,  which does not sum up to 60
thanks a lot for your help ! 
Jack

Captain_Jack909_0-1678438987441.png

 

Greg_Deckler
Super User
Super User

@Captain_Jack909 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hej Greg.

I have read the article, but maybe I am a little bit slow to follow, I have tried different things , but no sucess.
can you help to be more specific how to fix the caculation in my scenario .

 

thanks a lot !

Jack

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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