cancel
Showing results for
Did you mean:
Senior 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] )

```

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

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

```

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

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!

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 58 members 1,060 guests
Recent signins: