Group and Divide sum of column on sum of another column

[Christian87]

Hi everyone, 

I have a simple but extensive database :

Order #	Revenue Group	Revenue	# items sold
1000	1	200
1000	2	400
1000	3	800	2
1001	1	400
1001	2	800
1001	3	1600	4
SUM		4200	6

I want to create a card that could present the sum of Revenue pr #items sold pr order + the average revenue pr item sold. 

Any suggestion would be highly apreciated

Christian

[v-huizhn-msft · Accepted solution]

Hi @Christian87,

It's still confusing. You want to calculate the sum of average group by items sold and order? The average revenue pr item sold is (200+400+800+400+800+1600+4200)/(2+4+6)=700? If it is you can create a new table using the following formula by clicking "New Table" under Modeling on home page. 

NewTable=SUMMARIZE(Table,Table[items sold],Table[Order],"Sum revenue",SUM(Table[Revenue]))

Then create a calculated column in 'NewTable' to get the result.

result=NewTable[Sum revenue]+SUMX(ALL(Table),Table[revenue])/SUMX(ALL(Table),Table[item sold])

Best Regards,
Angelia