cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate Average for one column and Sum for other

Dear all

I have 50 products. I need to show 6 of them in separate table, since they are from the different group.

Below you can find the sample of my table. I have problem with the total row of the table.

Total product amount is right. Unit price is calculated from the different amount by dividing the product amount.

We see 4 in the bottom of the table. Actually when we divide Amount for all 50 products we get 4. But in this table I don't need to see 4. My desired result is average unit price if its possible.

And total amount (the third column) is not the total of the related column but product amount x 4 which is wrong.

 product amount unit price total amount Product A 235 145 34,075 Product B 186 314 58,404 Product C 269 462 124,278 Product D 57 664 37,848 Product E 1196 90 107,640 Product F 620 799 495,380 Total 2,563 4 10,252
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate Average for one column and Sum for other

Hi @OSS

You may try below measures.Attached the simplified sample file for your reference.

`Price = SUM(Table2[total amount])/SUM(Table2[product amount])`
```Unit price =
IF (
HASONEVALUE ( Table2[Product] ),
[Price],
AVERAGEX ( SUMMARIZE ( Table2, Table2[Product] ), [Price] )
)
```
```Total =
SUM ( Table2[product amount] ) * CALCULATE ( [Price], ALL ( Table2 ) )
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Calculate Average for one column and Sum for other

Hi @OSS

You may try below measures.Attached the simplified sample file for your reference.

`Price = SUM(Table2[total amount])/SUM(Table2[product amount])`
```Unit price =
IF (
HASONEVALUE ( Table2[Product] ),
[Price],
AVERAGEX ( SUMMARIZE ( Table2, Table2[Product] ), [Price] )
)
```
```Total =
SUM ( Table2[product amount] ) * CALCULATE ( [Price], ALL ( Table2 ) )
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculate Average for one column and Sum for other

Dear @v-cherch-msft