cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
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!

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.

View solution in original post

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 IV
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!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors