## Desktop

Frequent Visitor
Posts: 13
Registered: ‎04-25-2018

# Return a sum for the max number in a group

Hello!

I feel like this should be simple, but for some reason I am not able to come up with a solution that works. I am trying to calculate the green column named Total for Max Version using a dax statement in a calculated colum.

Here is an example of my dataset:

Basically, I want the value in the "Total Responses" Column only for the max Version of every GroupKey.

The Total Responses column is a calculated column using the following formula:

Total Responses = CALCULATE(SUM(Documents[Responses]),
ALLEXCEPT(Documents,Documents[GroupKey])

)

Is there a way to include a filter to retun only for the max version in this calculation so I don't need the other column? How can I best accomplish this?

Thanks!

Accepted Solutions
Highlighted
Super User
Posts: 2,823
Registered: ‎09-27-2017

## Re: Return a sum for the max number in a group

@cduncan19871

Try this

```=
IF (
[Version]
= CALCULATE (
MAX ( Documents[Version] ),
ALLEXCEPT ( Documents, Documents[GroupKey] )
),
CALCULATE (
SUM ( Documents[Responses] ),
ALLEXCEPT ( Documents, Documents[GroupKey] )
)
)
```

All Replies
Highlighted
Super User
Posts: 2,823
Registered: ‎09-27-2017

## Re: Return a sum for the max number in a group

@cduncan19871

Try this

```=
IF (
[Version]
= CALCULATE (
MAX ( Documents[Version] ),
ALLEXCEPT ( Documents, Documents[GroupKey] )
),
CALCULATE (
SUM ( Documents[Responses] ),
ALLEXCEPT ( Documents, Documents[GroupKey] )
)
)
```
Super User
Posts: 2,223
Registered: ‎09-19-2016

## Re: Return a sum for the max number in a group

To what I can understand you simply want to sum the total responsed per Group correct?

If you add the GroupKey in a table visual and then the Responses and select the summarize you will get the values you need, the only questionis the groups without answers in this case just create the following measure:

`Response measures = SUM(Documents[Responses]) + 0`

This will return the following  visual:

The aggregation level of the response totals will be based on the rows you add as context on your visuals

Regards,

MFelix

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

Proud to be a Datanaut!

Frequent Visitor
Posts: 13
Registered: ‎04-25-2018

## Re: Return a sum for the max number in a group

Hi there!

So, I needed the sum to be in the correct row-ie: in the row of the max version number per group. These particular rows are going to be flagged and used in another calculation, if that makes sense. I probably will not use these sums in any visualizations, just in the data table.

Thanks!

Super User
Posts: 2,223
Registered: ‎09-19-2016

## Re: Return a sum for the max number in a group

Not sure if I understand the reason behind it but you can redo the measure to this:

```Response measures =
IF (
SELECTEDVALUE ( Documents[version] )
= CALCULATE (
MAX ( Documents[version] );
FILTER (
ALL ( Documents[GroupKey] );
Documents[GroupKey] = MAX ( Documents[GroupKey] )
);
ALL ( Documents[version] )
);
CALCULATE (
SUM ( Documents[Responses] ) + 0;
ALLEXCEPT ( Documents; Documents[GroupKey] )
);
BLANK ()
)```

As you can see only on the max version you have values even when placing the version as a context

Not meaning to take any merit from @Zubair_Muhammad (a great datanaut and PBI expert) but just giving other ways of approaching the issue without having columns added to the model and additional weight to it.

Regards,

MFelix

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

Proud to be a Datanaut!