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 for Max-value per Sub-category

Hello All

I am new towards Power BI , and I am struggling with getting a sumx for max-value per subcategory. really appreciate if someone can help me !

 

I have a table which contains dimensions : category , subcategory and FC round as below picture .

Captain_Jack909_0-1678134290304.png

What I would like to achive is to get the value per subcategory from the max FC round on category level, in this case the max FC round is 202302.
I tried the dax : 

SUMX(SUMMARIZEfcinput,fcinput[category],"MaxRound_FC_value",
CALCULATE(SUM(fcinput[sum]),FILTER(fcinput,fcinput[FC round]=MAX(fcinput[FC round])))),[MaxRound_FC_value])

But I got an incorrect result as below which the yellow record should not be there, since it belongs to FC round 202301.
however the Total aggregation is correct.

Captain_Jack909_1-1678134676897.png
Jack
thanks in advance!

 

3 REPLIES 3
RiemuRiku
Frequent Visitor

Hi!

Had similiar issue, ran into your post.
Solved it this way in my case, hope you can use it too!

 

CumulativeZoneWeight_WithScopeAndTotal = 
VAR subCategoryMax = 
SUMMARIZE(Z_KrussMain, //returns table
Z_KrussMain[ZoneNr], //GroupBy columns
"Weight",MAX(Z_KrussMain[CumulativeZoneWeight])) // Columns to create

RETURN
IF(ISINSCOPE(Z_KrussMain[ZoneNr]),
MAX(Z_KrussMain[CumulativeZoneWeight]), // in scope, sublevel data. -> showing sublevel data
SUMX(subCategoryMax,[Weight]) // Sum the previously generated table
)

 

 

 

olgad
Super User
Super User

Hi, 

olgad_0-1678144136960.png

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Thanks a lot for the help, Syndicate.

in the my data table, actually there are different categories, I tried the formular its correct on each row but the total is not sumed up , it seems it only sumx by the max FC round from the whole table

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors