cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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.

1 ACCEPTED SOLUTION
Community Support

Hi @enoch99 ,

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

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.

9 REPLIES 9
Community Support

Hi @enoch99 ,

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

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.

Helper I

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

Super User
``````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 )
)
``````

Proud to be a Super User!

Helper I

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?

Super User

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

Proud to be a Super User!

Helper I

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

Helper I

@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.

Helper I

Please find the PBI file here

Helper I

@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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors