Sum with distinct price values

[gianmarco]

Dear All,

I have the following database:

Product Name	Quantity	Unitary Price
Apple	2	0,60
Apple	1	0,60
Orange	4	0,80
Banana	3	0,90
Banana	2	0,90
Banana	4	0,90

I need to visualize a report with total quantity of distinct products along with their distinct unitary price and total price.

My table should looks like the following:

Product Name	Quantity	Unitary Price	Total Price
Apple	3	0,60	1,80
Orange	4	0,80	3,20
Banana	9	0,90	8,10

My problem is that Power BI gives me back the sum of unitary prices per product name, just like the quantities. It is good for the latter, but I don't want it for the former.

What should I do in this case?
Thank you guys

[Fowmy]

@gianmarco 

How did you calculate 2.4 and it should be 3.2 ?

You create the following measure:

Total Price = SUM(Table[Quantity])  * AVERAGE(Table[Unitary Price] )

[gianmarco]

Dear @Fowmy ,

I already did that, but the total price in bold characters is wrong. It's because I do the average of the unitary price. It must be a way for which Power BI automatically associates the unitary price to the product name so I don't need to estimate the average.

[Fowmy]

@gianmarco 

Please show me the expected results

[gianmarco]

The expected result should be:

Product Name	Quantity	Unitary Price	Total Price
Apple	3	0,60	1,80
Orange	4	0,80	3,20
Banana	9	0,90	8,10
Total	16	--- (no value)	13,1

[Fowmy · Accepted solution]

@gianmarco 
This should work

Total Price = 
SUMX(
    VALUES(Table9[Product Name]),
    CALCULATE(SUM(Table9[Quantity])  * AVERAGE(Table9[Unitary Price] ))
)

[gianmarco]

That's great... Thanks @Fowmy !

[gianmarco]

Yeah, my fault. It was a mistake. Thanks. I just corrected the table.

[Fowmy]

@gianmarco 

Check the solution and accept it if it works for you