cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imagautham
Helper II
Helper II

Getting the Maximum number across multiple columns

Hello,

 

I have data like the below, I need to write a measure to find the maximum value across these 2 columns.

The measure should return 900 in the below case.

I tried using MAXX but unable to figure out. How do I proceed with this?

 

table_sc.png

3 ACCEPTED SOLUTIONS
ddpl
Solution Sage
Solution Sage

@imagautham  use this

 

Measure = Var maxproduct1 = MAX('Table'[Prodcut 1 sales])
          Var maxproduct2 = MAX('Table'[Prodcut 2 sales])
          return
          if(maxproduct1 >maxproduct2,maxproduct1,maxproduct2)
Please accept as solution if its worked
 

View solution in original post

The MAX function will also return the larger of two scalar values, so you could use both functions of MAX like this:

MAX ( MAX ( 'Table'[Product 1 sales] ), MAX ( 'Table'[Product 2 sales] ) )

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

That is not the ideal structure to have.  In the Query Editor, you should select the first column, right click and select "Unpivot Other Columns".  Then write this measure and drag it to a card visual

Max value = max(Data[Value])

Hope this helps. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
ddpl
Solution Sage
Solution Sage

@AlexisOlson , @Ashish_Mathur ,

 

Thanks for suggesting the optimize way to solve this problem,

Appreciate it.

 

I request @imagautham to please change the accepted solution and assign it to the best one, so that others will also get it easily.

 

Thanks all again.

Ashish_Mathur
Super User
Super User

Hi,

That is not the ideal structure to have.  In the Query Editor, you should select the first column, right click and select "Unpivot Other Columns".  Then write this measure and drag it to a card visual

Max value = max(Data[Value])

Hope this helps. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Hi Ashish. Thanks for the feedback.

I'm a newbie to Power BI. Can you explain why the above approach is not ideal?

Hi,

Because you will not be able to create a Product slicer and your measure will be very long and cumbersome to write if you had 10 products (and there 1 column for each product).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
imagautham
Helper II
Helper II

Thanks @ddpl  for helping me out

ddpl
Solution Sage
Solution Sage

@imagautham  use this

 

Measure = Var maxproduct1 = MAX('Table'[Prodcut 1 sales])
          Var maxproduct2 = MAX('Table'[Prodcut 2 sales])
          return
          if(maxproduct1 >maxproduct2,maxproduct1,maxproduct2)
Please accept as solution if its worked
 

The MAX function will also return the larger of two scalar values, so you could use both functions of MAX like this:

MAX ( MAX ( 'Table'[Product 1 sales] ), MAX ( 'Table'[Product 2 sales] ) )

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors