Forum Discussion

Captain_Jack909's avatar
Captain_Jack909
Frequent Visitor
3 years ago

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 .

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(SUMMARIZE( fcinput,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.


Jack
thanks in advance!

 

Need Help

3 Replies

  • RiemuRiku's avatar
    RiemuRiku
    Frequent Visitor
    2 years ago
    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
)

     

     

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      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