Calculated Column based on Multiple Rows

[Moody_Data]

Hello All,

I am looking to create what I think is a fairly simple calculated column that will calculate the 'Order Revenue' column below. 

The unique order number may contain multiple rows.  The order revenue column should be calculated only on the highest priced unit within that order.  Here is how the table is organized in Power BI:

Order Number	Unit	Price	Order Revenue
123	Case	2	0
123	Pallet	10	10
456	Case	2	2
789	Case	2	0
789	Pallet	10	10

I am looking to create the 'Order Revenue' column in red above.  If there is an order with multiple different units, the new column will display just the max value of the price.

Thank you in advance and please let me know if further explanation is needed.

[V-pazhen-msft · Accepted solution]

@Moody_Data 

You could just drag ordernumber and price into the table visual, and use maximum categorize for price. 

If you want to have the exact output in calculated column, the idea is you would need to create a index column, and if the order number for current row = previous role, then blank, if they are not the same order number you can return MAX(price) for the order number. Something like: 

Column = var maxprice = CALCULATE(MAX('Table'[Price]),FILTER('Table','Table'[Order Number]=EARLIER('Table'[Order Number]))) Return if([Order Number]= CALCULATE(MAX('Table'[Order Number]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1)),BLANK(),maxprice) 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Moody_Data]

Thank you @V-pazhen-msft , I created the new index column and with the expression provided in the solution, the Order Revenue column now displays only the max price for those orders that contain multiple lines.

Much appreciated!

[amitchandak]

@Moody_Data , Create a new column like

Order Revenue = if([Price]= maxx(filter(Table, Table[Order Number] =earlier(Table[Order Number])),Table[Price]),[Price],blank())

[Moody_Data]

@amitchandak , Many thanks for the response.

This is very close to working but I may have left out a scenario from the original post.

If there is an order that contains multiple rows of only the same item, it is returning the max value of both rows:

Order Number	Unit	Price	Order Revenue
987	Case	2	2
987	Case	2	2

Ideally would appear as the below so that each order is assigned only 1 price each:

Order Number	Unit	Price	Order Revenue
987	Case	2	0
987	Case	2	2

I will also take a look - thank you for any ideas on how to modify this to just show 1 price per order.