cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

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

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

Top Solution Authors
Top Kudoed Authors