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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.