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

[gggar]

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

[amitchandak · Accepted solution]

@gggar , Create a new measure like this and  use

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

[v-jayw-msft · Accepted solution]

Hi @gggar ,

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

https://docs.microsoft.com/en-us/dax/all-function-dax 

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

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

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

Best Regards,

Jay

[gggar]

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!

[amitchandak · Accepted solution]

@gggar , Create a new measure like this and  use

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