Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
enoch99
Helper I
Helper I

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

Hello all,

 

I have data similar like below:

IDMonthPlaceFemale StudentsMale StudentsTotal
1JanuaryPlace_125010260
2JanuaryPlace_25050100
3FebruaryPlace_110050150
4FebruaryPlace_117525200
5FebruaryPlace_2100100200
6FebruaryPlace_220050250
7MarchPlace_120050250
8MarchPlace_110190200
9MarchPlace_2100200300
10MarchPlace_2300100400

 

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
v-binbinyu-msft
Community Support
Community Support

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

vbinbinyumsft_0-1713511768833.png

 

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

 

vbinbinyumsft_1-1713511797349.png

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.

View solution in original post

9 REPLIES 9
v-binbinyu-msft
Community Support
Community Support

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

vbinbinyumsft_0-1713511768833.png

 

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

 

vbinbinyumsft_1-1713511797349.png

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.

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

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

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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?

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


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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

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

Please find the PBI file here

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

 

enoch99_0-1713186644060.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.