cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Weighted Average Calculation in Power BI

Hi all, any advice in relation to the following problem would be greatly appreciated.

Background to problem:

Data related to products is stored in a database. Data for two attributes is displayed on tiles – when a single product is selected it will display the attribute value for that product, when there is a multiple selection it needs to display a weighted average for that selection of products.

Each product has a different value of sales in a year, which is stored in local currency. The requirement is to show the weighted average by sales of that attribute. To do this I have converted the local currency values into a consistent currency e.g. GBP.

Where a value for an attribute is NULL, this should be excluded from the weighted average calculation. I am having difficulty calculating the weighted average.

Example:

In excel I have applied the logic for the weighted average calculations:

Question – how would you implement a complex calculation like this into Power BI, using DAX? The tile must show the attribute 1 value for any given product when only that is selected and then the tile must display the weighted average when multiple products are selected.

1 ACCEPTED SOLUTION
Super User

Have a look the following two measures.

``Sum of Sales = SUM( Data[Sales] )``
``````Weighted Average 1 =

VAR AllSales =
CALCULATE (
[Sum of Sales],
REMOVEFILTERS()
)

VAR Numerator =
SUMX(
Data,
Data[Attribute 1] * DIVIDE( [Sum of Sales], AllSales )
)

VAR Denominator =
CALCULATE(
SUMX(
Data,
DIVIDE( [Sum of Sales], AllSales )
),
NOT ISBLANK(Data[Attribute 1])
)

RETURN DIVIDE(Numerator, Denominator)``````

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
2 REPLIES 2
Super User

Have a look the following two measures.

``Sum of Sales = SUM( Data[Sales] )``
``````Weighted Average 1 =

VAR AllSales =
CALCULATE (
[Sum of Sales],
REMOVEFILTERS()
)

VAR Numerator =
SUMX(
Data,
Data[Attribute 1] * DIVIDE( [Sum of Sales], AllSales )
)

VAR Denominator =
CALCULATE(
SUMX(
Data,
DIVIDE( [Sum of Sales], AllSales )
),
NOT ISBLANK(Data[Attribute 1])
)

RETURN DIVIDE(Numerator, Denominator)``````

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Super User

Should have said the name of the table I used is data.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors