Power BI - Show Sum of column in table but show average for each line

Question

I have data for orders which have a row for each line item in the order, and I would like to summarise the data for each order by grouping the total of each order (by order number) and I then have another page to drill down into the line items of a particular order. At the moment I have set up an SQL view that has entries for each line item of the orders with a column for the total of the whole order (this number is repeated over all the entries for a particular order number). Below is an examples of the SQL view I am depicting:

Order_Number	Line_Item_Number	Total_for_Order
1	1	$2000
1	2	$2000
1	3	$2000
2	1	$1000
2	2	$1000

What I would like to have in Power BI is to show the data in a table showing only one line per order number but summarising the column for the totals with the sum of the column. At the moment I am having trouble because in order to summarise the data in the table I could use either max, average, or min, on the Total_for_Order column, but then the "Total" at the bottom of the Power BI table becomes a max, average, or min of the column. Basically I would like to see a table as below:

Order_Number	Total_for_Order
1	$2000
2	$1000
Total	$3000

What is the best way to achieve this? Is it better to create a new SQL view, or is there a way to sum the column of the Power BI table even though I would like to show an average for the Total_for_Order rows?

Thanks, and I hope this makes sense!

Answer 1

Starting table:

Create a measure:

Measure = 

VAR t = SUMMARIZE('Table', 'Table'[Order_Number], "@max", CALCULATE(MAX('Table'[Total_for_Order])))
RETURN 
SUMX(t, [@max])

Create a visual: