Sum of MAX Results

[Anonymous]

What formula can I use to get the sum of all MAX items?
The sum of these MAX by Line items is 3.13...how do I get that with a formula?  I used this formula to get the MAX figures by line:

MaxEarnedHours = max('ATWOQ300 - Current'[Earned Hours Sum])

Line      MaxEarnedHours

323          .21

322          1.60

380          .50

310          .46

382          .36

[Greg_Deckler]

@Anonymous 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.

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

=SUMX(VALUES('ATWOQ300 - Current'[Line]),[MaxEarnedHours])

If it does not, then share the link from where i can download your PBI file.

[pranit828]

Hi @Anonymous 

_SumOfMax = SUMX(
SUMMARIZE('ATWOQ300 - Current', 'ATWOQ300 - Current'[Line], "MaxOfSum", max('ATWOQ300 - Current'[Earned Hours Sum]),

[MaxOfSum])

[pranit828]

HI @Anonymous 

try

MaxEarnedHours = calculate(sumx('ATWOQ300 - Current',[MaxEarnedHours]), ALLEXCEPT('ATWOQ300 - Current','ATWOQ300 - Current'[Line]))

[Anonymous]

Thx.  This retuned a result of 1196 vs the 3.13.

[amitchandak]

@Anonymous , Not very clear. The context till where we need to take max. It that line the formula will be like

MaxEarnedHours = sumx(values('ATWOQ300 - Current'[line]),max('ATWOQ300 - Current'[Earned Hours Sum]))

or

MaxEarnedHours = sumx(summarize('ATWOQ300 - Current'[line],"_1",max('ATWOQ300 - Current'[Earned Hours Sum])),[_1])

[Anonymous]

This formula worked perfect...THX!
=SUMX(VALUES('ATWOQ300 - Current'[Line]),[MaxEarnedHours])
I tried to select that this solved the issue in the thread and it errored out twice.

[Ashish_Mathur]

You are welcome.

[Anonymous]

Thx for the quick response.  I used the formula: MaxEarnedHours = sumx(values('ATWOQ300 - Current'[line]),max('ATWOQ300 - Current'[Earned Hours Sum])) and it returned a value of 8 instead of 3.13.  Thoughts?