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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Alienvolm
Helper IV
Helper IV

DAX: Calculate MAX value of an aggregated column

Hi, 

I have a table that gathers daily values. I have extracted a table for the single users and created a summary table that gathers different information at the user level. 

 

For each user, I now need to calculate the maximum value of their monthly consumption. Example:

 

Alienvolm_0-1634073628121.png  

Alienvolm_1-1634073657335.png

Minute consumption is a measure (SUM of the daily entries for consumption). I need to calculate the maximum value by row (max monthly consumption by user). 

 

In my resulting table, my Max Monthly Consumption is a calculated column. 

 

How can I do that? 

 

Thanks! 

 

~Alienvolm

1 ACCEPTED SOLUTION

OK. I've tested the following:

Max by User Display name =
CALCULATE (
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Teams PSTN Records', 'Teams PSTN Records' [User Display name], 'Dates [Month-Year] ),
            "_Total", [Minute consumption]
        ),
        [_Total]
    ),
    ALLEXCEPT ( 'Teams PSTN Records', 'Teams PSTN Records' [User Display name] )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@Alienvolm this is what you need

 

Max Consumption = 
MAXX ( 
    ALLSELECTED ( 'Table'[User Display Name] ), 
    [Your Consumption Measure]
)

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Alienvolm was not clear from your original post, try @PaulDBrown solution, if it works then you are good to go. Cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Alienvolm create a measure:

 

Max Consumption = 
SUMX ( VALUES ( Table[User] ), CALCULATE ( MAX ( Table[Monthly Consumption] ) ) )

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k,

 

Thanks for the suggestion... So... I applied your code as below: 

 

 

Max consumption = 
    SUMX(
        VALUES(
            Users[User Display name]), 
            CALCULATE(
                MAX('Teams PSTN Records'[Duration (m)])
            )
    )

However, that still gives me the MAX duration in that period (day with max duration), not for the sum of that period (mm-yy):

Alienvolm_0-1634137778026.png

In this case, I want to know the MAX for the values in each row. For example:

Alienvolm_1-1634137979678.png

The Max consumption for Wilfried was 574 (value of July). 

Thanks for the prompt though! Trying to figure it out...

Try:

Max value = MAXX(ALLEXCEPT(Table, Table [User Display name]), [Minute Consumption])





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@parry2k Sorry for the confusion... 

 

@PaulDBrown That doesn't work either... it gives me some other aggregation:

Alienvolm_0-1634139519622.png

Code:

Max consumption = 
MAXX(
    ALLEXCEPT(
        Dates, 
        Dates[Month-Year]
        ), 
    [Minute consumption]
)

Thanks all the same!

Apologies. The original response referenced the month field in ALLEXCEPT, when it should be the User Display name as in the edited post. So...

 

Max value = MAXX(ALLEXCEPT(Table, Table [User Display name]), [Minute Consumption])

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I still get the same result... I think I'm missing the aggregation/summarization for the month. somehow...

 

Alienvolm_1-1634141013200.png

 

Code: 

Max consumption = 
MAXX(
    ALLEXCEPT(
        'Teams PSTN Records',
        'Teams PSTN Records'[User Display Name]
    ),
        [Minute consumption]
)

 

OK. I've tested the following:

Max by User Display name =
CALCULATE (
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Teams PSTN Records', 'Teams PSTN Records' [User Display name], 'Dates [Month-Year] ),
            "_Total", [Minute consumption]
        ),
        [_Total]
    ),
    ALLEXCEPT ( 'Teams PSTN Records', 'Teams PSTN Records' [User Display name] )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown

 

It worked! 

 

Thank you so much! 🙂

 

 ~Alienvolm 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors