Forum Discussion
MAX VALUE IN A ROW
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
Hello there Anonymous ! 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 GeraldesYou can use following formula to get Max in PQ
=List.Max({[Sunday],[Monday],[Tuesday]})
7 Replies
Hello there Anonymous ! 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 Geraldesgoncalogeraldes Anonymous 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?
You can use following formula to get Max in PQ
=List.Max({[Sunday],[Monday],[Tuesday]})
