Return a sum for the max number in a group

[Anonymous]

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! 

[MFelix]

Hi @Anonymous,

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

[Anonymous]

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!

[MFelix]

Hi @Anonymous,

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this

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