- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Return a sum for the max number in a group

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 11:46 AM

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!

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 12:41 PM

Try this

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

All Replies

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 12:41 PM

Try this

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 12:45 PM

Hi @cduncan19871,

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 12:56 PM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018 01:13 PM

Hi @cduncan19871,

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!