Finding Average Unit Price Across Invoices

[cparker4486]

Very new to Measures and struggling to wrap my head around this. It's probably going to be quite simple once I see the answer.

Invoice	Product	Quantiy	Unit Price
INV001	Apple	3	$5.00
INV002	Apple	2	$4.50
INV003	Apple	7	$7.22

You can see that the Apple is not always sold for the same Unit Price. I want to answer the following question, "What is average unit price for the Apple?"

The answer is (5 + 4.5 + 7.22)/3 = 5.57.

My data table layout is quite different but I made a measure that uses DISCOUNTCOUNT() to count the number of invoices in my fact table and I then used that in another measure in my dimensions table.

Avg. Unit Price = SUMX('Invoice Lines (2) Dim', SUM([Unit Price]) / [Distinct Order Count])

This gives me a wildly high number so I'm obviously not thinking about this the right way.

[v-hashadapu]

Hi @cparker4486 , Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

[Shahid12523 · Accepted solution]

Avg Unit Price Per Invoice =
AVERAGEX(
VALUES('Invoice Lines (2) Dim'[Invoice]),
CALCULATE(AVERAGE('Invoice Lines (2) Dim'[Unit Price]))
)

This gives you the average unit price per invoice, not weighted by quantity—just like your example. Clean, accurate, and context-aware.

[Ashish_Mathur]

Hi,

Wouldn't you want to calculate the weighted average price instead?

[cparker4486]

That would also be good to know. Please provide the formula if you can. Thank you.

[Ashish_Mathur]

Try this measure

Avg. Unit Price = SUMX('Invoice Lines (2) Dim', 'Invoice Lines (2) Dim'[Unit Price]*'Invoice Lines (2) Dim'[Quantity]) /sum('Invoice Lines (2) Dim'[Quantity])

[ryan_mayu]

@cparker4486 

you can try this 

Average Unit Price = AVERAGE('Sales'[Unit Price])

If this does not work, pls provide the whole sample data. It's becuase it looks like you have more than one table. 

pls provide the sample data and expected output.