Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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] )
)
Proud to be a Super User!
Paul on Linkedin.
@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.
@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.
@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):
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...
Try:
Max value = MAXX(ALLEXCEPT(Table, Table [User Display name]), [Minute Consumption])
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:
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])
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...
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] )
)
Proud to be a Super User!
Paul on Linkedin.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
141 | |
109 | |
69 | |
55 |