Reply
Frequent Visitor
Posts: 8
Registered: ‎04-25-2018
Accepted Solution

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:

SampleData.png

 

 

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,615
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] )
    )
)

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,615
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,035
Registered: ‎09-19-2016

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

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:

responses.png

 

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: 8
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,035
Registered: ‎09-19-2016

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

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

 

resp.png

 

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!