MAX VALUE IN A ROW

[dsmitha]

Hi,

am new to PBI and PQ,  request your help regarding how to add a new column by finding  max value in a row.  table format details are attached herewith. am looking to add a column which will give the maximum collection of a busnumber by comparing collecitons on Sunday, Monday and Tuesday .

for eg: KNR01 maximum collection is on Monday so in the new column i want to show value of Monday

[goncalogeraldes · Accepted solution]

Hello there @dsmitha ! To get your desire result, I would recommend the following:

1. Go to PQ, and select your "busnumber" and "SEATINGCAPACITY" columns and unpivot the other columns. You then get a "Attribute" and a "Value" column where your Attribute becomes the "Day" and the Value becomes the seatings (I suppose)

2. Close and Apply PQ

3. Add the following measure 

Max Value =
CALCULATE (
    SELECTEDVALUE ( Table[busnumber] ),
    FILTER ( Table, [Value] = MAX ( Table[Value] ) )
)

4. Add the measure to your table visual and check the results.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Vijay_A_Verma · Accepted solution]

You can use following formula to get Max in PQ

=List.Max({[Sunday],[Monday],[Tuesday]})

[dsmitha · Accepted solution]

Hi Vijay,

Thanks a lot

👍

[Vijay_A_Verma · Accepted solution]

You can use following formula to get Max in PQ

=List.Max({[Sunday],[Monday],[Tuesday]})

[dsmitha]

Hi Vijay,

i would like to get the result as max value + the day

for eg: 6100 -> Monday

kindly advise

[dsmitha · Accepted solution]

Hi Vijay,

Thanks a lot

👍

[goncalogeraldes · Accepted solution]

Hello there @dsmitha ! To get your desire result, I would recommend the following:

1. Go to PQ, and select your "busnumber" and "SEATINGCAPACITY" columns and unpivot the other columns. You then get a "Attribute" and a "Value" column where your Attribute becomes the "Day" and the Value becomes the seatings (I suppose)

2. Close and Apply PQ

3. Add the following measure 

Max Value =
CALCULATE (
    SELECTEDVALUE ( Table[busnumber] ),
    FILTER ( Table, [Value] = MAX ( Table[Value] ) )
)

4. Add the measure to your table visual and check the results.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Khushboo9966]

@goncalogeraldes @dsmitha @Vijay_A_Verma How to print the max # of that row in this case? For example: For KNR001, The max of value of Sunday, Monday, Tuesday is 6100. How can the result be 6100?

[dsmitha]

Hi goncalogeraldes,

i would like to get the result as max value + the day

for eg: 6100 -> Monday

kindly advise

[dsmitha · Accepted solution]

Hi Goncalogeraldes,

Thank you so much 

👍