Forum Discussion
Solved
Weighted Average in a Hierarchy Visualized by a Matrix
I am trying to create a measure to show the weighted average at multiple levels of a hiearchy. I will be presenting this data in a matrix visualization in Power BI. I will provide sample data to il...
Try this:
Avg = DIVIDE ( SUMX ( Table1, Table1[Unit Price] * Table1[Qty Sold] ), SUMX ( Table1, Table1[Qty Sold] ) )
AlexisOlson
Super User
Super UserTry this:
Avg =
DIVIDE (
SUMX ( Table1, Table1[Unit Price] * Table1[Qty Sold] ),
SUMX ( Table1, Table1[Qty Sold] )
)
Thank you! This is perfect! However, I found one anomaly during my validation--maybe you will know what is causing it. One of the values at the lowest granularity came up as a lower value compared to the actual. But others seemed unafflicted by this. In this example I get ~$87k where I would expect ~$155k.
- AlexisOlson
Is your Unit Price column using a SUM aggregation? I bet you have more than one row in your data table for 1A and it's adding the prices together like it does for the subtotals.
You nailed it. Duh. I should have realized that. I have what I need now and it's working perfectly. Thank you so much!
