groupby count to find avg price

[shalabh]

Hi,

I am trying to find the avg price per unit. 

The issue is my data can have an order ID with price borken, but quantity same.

For example

ID	Product	Qty	Price
A	x	2	10
A	x	2	20
B	x	5	30
C	x	3	20
C	x	3	40

Order ID A - quantity is 2 and total price is 30 (it is split based on SKU). Given this, avg price for A is 30/2=15 

For the above data set, avg price is (120/10 = 12) : price is added, and qty is total for each ID divided by count of each ID

How can I write a DAX measure to compute this?

In excel, I would run a pivot and do it row wise for each ID. 

Please note, I would like to use a slicer on product later to find avg price by product. Hence summarizecolumns may not be the best way

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

It is for creating a dax measure.

Avg price: = 
VAR _qty =
    SUMX (
        VALUES ( Data[Id] ),
        CALCULATE ( DIVIDE ( SUM ( Data[Qty] ), COUNTROWS ( Data ) ) )
    )
VAR _pricetotal =
    SUM ( Data[Price] )
RETURN
    DIVIDE ( _pricetotal, _qty )