DAX to find the MAX Number of a summed column

[DebbieE]

Another DX Query Im struggling with. I want to end up with a card. Heres some fake data

Ref Stage Person Hours
1     1        1            3 
1     2        1            5
1     3         1           1
1     3         2           4
1     4        1            7
1     6        2            4
1     7        3            3
2    1         4            7

First of all i want to SUM Hours By Ref and Person. So Person 1 for Ref 1 woulkd be 15 hours
And the card would show the MAX Hours.  So for this example Its person 1 with 15 Hours so this would show in the card

I dont really know where to start on this one.  Here is the SQL Query that works on the data

SELECT MAX(Hours)
FROM (

select Ref, Person 
SUM(Hours ) AS Hours
FROM table1 
GROUP BY Ref, Person
) a
GROUP BY [Incident Ref]

The report is already filtered on a single Reference

[parry2k · Accepted solution]

@DebbieE try following two measures

first get sum by ref/person and then find the max hours

Sum = CALCULATE( SUM( Table3[hours] ), ALLEXCEPT( Table3, Table3[person], Table3[ref] ) )

MAX = MAXX( Table3, [Sum] )

[tex628 · Accepted solution]

Nevermind the previous measure, it's incorrect 🙂

Here:

Measure = 
SUMX(
TopN( 1;
    Summarize(
        'Table';
        'Table'[Ref];
        'Table'[Person];
        "Value" ; Sum('Table'[Hours])
); [Value] ); 
[Value])

[tex628]

Try this:
 

Measure = 
Calculate(
Selectedvalue([Person]) & " with " & Selectedvalue([Value]);
TopN( 1;
Summarize(
'Table';
'Table'[Ref];
'Table'[Person];
"Value" ; Sum('Table'[Hours])
); [Value] 
))

 

Dont know if it will work 😄 
The red values should be values created in the summarize statement. 

Let me know if it works!

[parry2k · Accepted solution]

@DebbieE try following two measures

first get sum by ref/person and then find the max hours

Sum = CALCULATE( SUM( Table3[hours] ), ALLEXCEPT( Table3, Table3[person], Table3[ref] ) )

MAX = MAXX( Table3, [Sum] )

[tex628 · Accepted solution]

Nevermind the previous measure, it's incorrect 🙂

Here:

Measure = 
SUMX(
TopN( 1;
    Summarize(
        'Table';
        'Table'[Ref];
        'Table'[Person];
        "Value" ; Sum('Table'[Hours])
); [Value] ); 
[Value])

[DebbieE · Accepted solution]

I just dont know what Im doing wrong.

On my actual data set I have 1 ref and the max hour is 4.4 hours

However this shows 17.60 hours. I cant even figure out where its getting that number from

Thank goodness I changed it to this and it works

Max Duration Hours lost = MAXX(
TOPN(1,SUMMARIZE('fact OMSIncident','dim OMSIncident'[Incident Ref],
'dim OMSInterruptions'[Pro Number],
"Value",SUM('fact OMSIncident'[Duration (Hours)])
),[Value]),
[Value])