Forum Discussion
Finding Average Unit Price Across Invoices
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])
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.
6 Replies
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.
Hi,
Wouldn't you want to calculate the weighted average price instead?
That would also be good to know. Please provide the formula if you can. Thank you.
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])
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.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!
