cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OSS Regular Visitor
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.

 

Please help me to calculate appropriate grand total by summing up the column values.  

 

 

 product amountunit pricetotal amount
Product A235145                34,075
Product B186314                58,404
Product C269462             124,278
Product D57664                37,848
Product E119690             107,640
Product F620799             495,380
Total              2,5634                10,252
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
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.
OSS Regular Visitor
Regular Visitor

Re: Calculate Average for one column and Sum for other

Dear @v-cherch-msft 

 

I really appreciate your answer. Thank you for assistance.

Best Regards