cancel
Showing results for
Did you mean:
Visitor

## Group and Divide sum of column on sum of another column

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

1 ACCEPTED SOLUTION

Accepted Solutions
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

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