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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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