cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Christian87 Visitor
Visitor

Group and Divide sum of column on sum of another column

Hi everyone, 

 

I have a simple but extensive database :

Order #Revenue GroupRevenue# items sold
10001200 
10002400 
100038002
10011400 
10012800 
1001316004
SUM 42006

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Group and Divide sum of column on sum of another column

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

 

1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: Group and Divide sum of column on sum of another column

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