Getting the Maximum number across multiple columns

[imagautham]

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?

[ddpl · Accepted solution]

@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

[AlexisOlson · Accepted solution]

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

[ddpl]

@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 · Accepted solution]

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. 

[imagautham]

@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?

[Ashish_Mathur]

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

[imagautham]

Thanks @ddpl  for helping me out

[ddpl · Accepted solution]

@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

[AlexisOlson · Accepted solution]

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