DAX: Calculate MAX value of an aggregated column

[Alienvolm]

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

[parry2k]

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

[parry2k]

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

[parry2k]

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

[Alienvolm]

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

[PaulDBrown]

Try:

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

[Alienvolm]

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

[PaulDBrown]

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

[Alienvolm]

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

[PaulDBrown · 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] )
)

[Alienvolm]

@PaulDBrown, 

It worked! 

Thank you so much! 🙂

 ~Alienvolm