cancel
Showing results for
Did you mean:
Helper I

## Calculate MAX from a measure presented, across all rows, with multiple group by

Hello,

I have a question to calculate MAX from all values here.

"Date Unit" is coming from a table, called "Calendar"

"Satisfaction" is coming from another table, called "Opinion"

"Score" is a measure, which is SUM(Opinion[Value])

These 2 tables Calendar and Opinion have relationship between them, based on date column.

As you can see I have 2 group by here, by Date Unit and Satisfaction

I have 2 questions:

1. How to create a measure that will return 503 for all rows?
2. What is the correct measure to calculate Score divided by the above measure? So each row will have (from the top) 503/503, 160/503, 45/503, and so on, which is basically percent of max.

Anyone have the answer? Any help will be greatly appreciated! Thank you

2 ACCEPTED SOLUTIONS
Super User IV

@gggar , Create a new measure like this and  use

maxx(summarize(Opinion, Calendar[Date Unit], Opinion[satisfaction], "_1", [Score]),[Score])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Community Support

Hi @gggar ,

You could take a look at ALL() and ALLEXCEPT() function.

``````Measure = MAXX(ALL(Opinion),[score])

Measure 2 = MAXX(ALLEXCEPT('Opinion','Calendar'[date unit]),[score])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
3 REPLIES 3
Community Support

Hi @gggar ,

You could take a look at ALL() and ALLEXCEPT() function.

``````Measure = MAXX(ALL(Opinion),[score])

Measure 2 = MAXX(ALLEXCEPT('Opinion','Calendar'[date unit]),[score])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper I

Hi @amitchandak ,

Thank you for the reply.

1. Could you let me know what is "_1" here in the DAX?
2. It seems that we can tune this DAX a little bit

The Total is correct, which is 503. Is it possible to return the Total (this 503) to each row?

Thank you!

Super User IV

@gggar , Create a new measure like this and  use

maxx(summarize(Opinion, Calendar[Date Unit], Opinion[satisfaction], "_1", [Score]),[Score])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.