cancel
Showing results 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

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:

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
Community Champion

OK. I've tested the following:

Max by User Display name =
CALCULATE (
MAXX (
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] )
)

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

10 REPLIES 10
Super User

@Alienvolm this is what you need

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

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.

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.

Super User

@Alienvolm create a measure:

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

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.

Helper IV

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

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

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

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

Community Champion

Try:

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper IV

@parry2k Sorry for the confusion...

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

Code:

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

Thanks all the same!

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper IV

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

Code:

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

Community Champion

OK. I've tested the following:

Max by User Display name =
CALCULATE (
MAXX (
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] )
)

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper IV

It worked!

Thank you so much! 🙂

~Alienvolm

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.