Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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!
@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())
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |