DAX measure to sum fields associated with the MAX of a grouping

[enoch99]

Hello all,

I have data similar like below:

ID	Month	Place	Female Students	Male Students	Total
1	January	Place_1	250	10	260
2	January	Place_2	50	50	100
3	February	Place_1	100	50	150
4	February	Place_1	175	25	200
5	February	Place_2	100	100	200
6	February	Place_2	200	50	250
7	March	Place_1	200	50	250
8	March	Place_1	10	190	200
9	March	Place_2	100	200	300
10	March	Place_2	300	100	400

I wanted to a measure to calculate the summation of Total Students considering the MAX grouped by Month and Place. I was able to do this using:

Sum of Total Max =
SUMX (
SUMMARIZE ( Table,Table[Month], Table[Place], "Max Total", MAX(Table[Total] )),
[Max Total])

1. However, I also wanted a measure to calculate the Total Female Students  considering only the rows of where the Total is the Maximum of the grouping. In my case, I would add the rows of ID: 1,2,4,6,7 and 10. Can you please help me with a DAX code to achieve this? 

2. I also want to get the list of ID for which the total is the Maximum Total of the grouping (month, place)?

I am not sure if my question is clear, but feel free to drop me a request so I can further expain 

Thank you very much.

[v-binbinyu-msft · Accepted solution]

Hi @enoch99 ,

First of all, many thanks to @mh2587  for your very quick replies.

Please try below steps:

1. create a measure with below dax formula and add it to card visual

Measure =
VAR tmp =
    SUMMARIZE (
        'Table',
        [Month],
        [Place],
        "Max Total", MAX ( 'Table'[Total] ),
        "Concate Str",
            'Table'[Month] & 'Table'[Place]
                & MAX ( 'Table'[Total] )
    )
VAR tmp1 =
    SELECTCOLUMNS ( tmp, [Concate Str] )
VAR tmp2 =
    FILTER ( 'Table', [Month] & [Place] & [Total] IN tmp1 )
RETURN
    SUMX ( tmp2, [Female Students] )

2. create a new table with below dax formula

Table 2 =
VAR tmp =
    SUMMARIZE (
        'Table',
        [Month],
        [Place],
        "Max Total", MAX ( 'Table'[Total] ),
        "Concate Str",
            'Table'[Month] & 'Table'[Place]
                & MAX ( 'Table'[Total] )
    )
VAR tmp1 =
    SELECTCOLUMNS ( tmp, [Concate Str] )
VAR tmp2 =
    FILTER ( 'Table', [Month] & [Place] & [Total] IN tmp1 )
RETURN
    SELECTCOLUMNS ( tmp2, "ID", [ID] )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[enoch99]

Thank you very much @v-binbinyu-msft. Works like a charm 🙂 

[mh2587]

Total Female Students Max = //Try this one 
VAR MaxTotal =
    MAXX (
        SUMMARIZE ( Table, Table[Month], Table[Place], "Max Total", MAX ( Table[Total] ) ),
        [Max Total]
    )
RETURN
    CALCULATE (
        SUM ( Table[Female Students] ),
        FILTER ( Table, Table[Total] = MaxTotal )
    )

[enoch99]

Thank you @mh2587 for your swift response. C

Can you also help me with my second question, which is to get the IDs of the asscoaited MAX total for the group?

[mh2587]

Just Put ID column with that calculation in Visual you will get the associated IDs 

[enoch99]

I thought this would be not that very complicated, but I could not get an answer yet. Please , I could use the help of Super Users?

Thank you

[enoch99]

@mh2587 I hope you have seen my reply. I am not able to get it to work, and would be great if you could give some directios as to what I am doing wrong.

Thank you.

[enoch99]

Please find the PBI file here

[enoch99]

@mh2587 thank you.

I tried to create a Card and Table visualiations based on the measure you shared, but I am getting a Female Total of 300, while it should be 1,175. Similarly, as you can see in the table visualizations, the IDs are not showing filtered to show only the rows of the Max Total.