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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gggar
Helper I
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.

 

gggar_0-1621539440398.png

 

"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
amitchandak
Super User
Super User

@gggar , Create a new measure like this and  use

 

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

View solution in original post

v-jayw-msft
Community Support
Community Support

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

12.PNG

 

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.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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

12.PNG

 

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.
gggar
Helper I
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
    gggar_0-1621572870678.png

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

Thank you!

 

 

amitchandak
Super User
Super User

@gggar , Create a new measure like this and  use

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.