cancel
Showing results for
Search instead for
Did you mean:
Highlighted
DebbieE Established Member

## DAX to find the MAX Number of a summed column

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

3 ACCEPTED SOLUTIONS

Accepted Solutions Super User

## Re: DAX to find the MAX Number of a summed column

@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] )

```

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Feel free to email me with any of your BI needs. tex628 New Contributor

## Re: DAX to find the MAX Number of a summed column

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 Established Member

## Re: DAX to find the MAX Number of a summed column

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])```

4 REPLIES 4 Super User

## Re: DAX to find the MAX Number of a summed column

@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] )

```

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Feel free to email me with any of your BI needs. tex628 New Contributor

## Re: DAX to find the MAX Number of a summed column

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 :-D
The red values should be values created in the summarize statement.

Let me know if it works!

tex628 New Contributor

## Re: DAX to find the MAX Number of a summed column

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 Established Member

## Re: DAX to find the MAX Number of a summed column

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])```

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 40 members 910 guests
Recent signins:
• OwenAuger • power_firm • Jayendran • Tiago_Cirilo • ElizabethTachji • seenai Please welcome our newest community members:
• Tiago_Cirilo • abhishek_a4 • hunglai • dimsarin • DataPro • NumeroENAP • pamella14 