cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Weighted Average by Customer and Product

Hi all,

Sorry if this has been answered elsewhere, but looking for some hopefully easy help. I am new to Power BI having extensive knowledge in QlikSense. I know the logic around how this should be done (AGGR in QS) but am not finding the right/best way in DAX to handle.

Basically I would like to have the weighted average cost by customer and by product. A sample table and expected results are below. I'd like to pull this value in a whisker table with just one dot per customer and product - doing a straight average of Cost is misleading.

One small caveat - in the source data, there are different amount of rows per "cost per unit" per Customer as this is actually distribution customers, meaning Customer A might have 10 end-customers who buy through them at \$1.00, 5 customers who buy through them at \$0.80, etc. So we have to be careful about summing up Unit Cost and likely should use average/distinct instead.

I'm sure this is a mix of sumx, calculate, evaluate, etc but proving fruitless so far. Below is a table that I hope comes through ok.

Product Customer Cost per Unit        Units     PurchasedTotal      Cost Weighted Average Cost

 A Adams \$                   1.00 50 \$         50.00 A Adams \$                   0.80 100 \$         80.00 A Adams \$                   1.24 10 \$         12.40 \$                                       0.90 A Smith \$                   1.25 50 \$         62.50 \$                                       1.25 A Russel \$                   0.90 25 \$         22.50 \$                                       0.90 B Adams \$                25.00 21 \$       525.00 B Adams \$                30.00 10 \$       300.00 \$                                     26.61 B Smith \$                25.00 100 \$   2,500.00 B Smith \$                15.00 75 \$   1,125.00 \$                                     20.71
1 ACCEPTED SOLUTION
Super User

This is a measure but could also be a calculated column

Super User

This is a measure but could also be a calculated column

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors