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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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