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

## Calculating the sum of dynamically weighted values

When calculating weighted values for a weighted average, how can we calculate a value which is the sum of those weighted values?

In the example below:

• Total Quantity = SUM(Sheet1[Quantity])
• Total Selected Quantity = CALCULATE(SUM(Sheet1[Quantity]),ALLSELECTED(Sheet1[Name]))
• Proportion of Total Selected Quantity = [Total Quantity]/[Total Selected Quantity]
• Avg X = AVERAGE(Sheet1[X])
• Quantity Weighted Avg = [Proportion of Total Selected Quantity]*[Avg X]

What I want is to capture the sum of the Quantity Weighted Avg column in a value (which would be the quantity weighted average of all Name values selected).  This value is actually 7.73, but PowerBI is calculating it as SUM(Proportion of Total Selected Quantity)*Avg(X).

Would be optimal to calculate this using Direct Query.

Any ideas?

1 ACCEPTED SOLUTION
Community Champion

@JeremyCooper we need to know how your data is set up in the source

Here's another Measure which also worked for me - but again I don't know how your data is set up so... don't know if will work for you

```Quantity Weighted Average NEW =
IF (
COUNTROWS ( VALUES ( 'Sheet1'[Name] ) ) = 1,
[Proportion of Total Selected Quantity] * [Avg X],
SUMX (
VALUES ( 'Sheet1'[Name] ),
[Proportion of Total Selected Quantity] * [Avg X]
)
)```

See Picture...

5 REPLIES 5
Community Champion

@JeremyCooper If Owen's Measure gives you the right Total only - but not the right numbers per Name use this Measure instead

I don't know how your data is set up so the condition may have to be adjusted... Let me know if this works.

```Quantity Weighted Average =
IF (
ISFILTERED ( 'Sheet1'[Name] ),
[Proportion of Total Selected Quantity] * [Avg X],
SUMX (
VALUES ( 'Sheet1'[Name] ),
[Proportion of Total Selected Quantity] * [Avg X]
)
)```
New Member

Owen's measure gives the correct value at the Total level (which meets the immediate need, fantastic work!).

Sean's formula provides the correct numbers per Name but is showing the same simple average (9.62) at the Total level.

I created this workbook from single sheet in excel to illustrate the problem, but I've got Owen's measure to work correctly on the (more complicated) real data without a hitch.

The initial need has been met (thanks again!) but it would be great to combine the two into a single formula......also, I see this will only work in Import mode, which is fine, but curious if this type of calcualtion capability is something to be released in the near future as part of the Direcy Query toolbox?

Community Champion

@JeremyCooper we need to know how your data is set up in the source

Here's another Measure which also worked for me - but again I don't know how your data is set up so... don't know if will work for you

```Quantity Weighted Average NEW =
IF (
COUNTROWS ( VALUES ( 'Sheet1'[Name] ) ) = 1,
[Proportion of Total Selected Quantity] * [Avg X],
SUMX (
VALUES ( 'Sheet1'[Name] ),
[Proportion of Total Selected Quantity] * [Avg X]
)
)```

See Picture...

New Member

This does it, thanks Sean!  I've tweaked it for the real data and it's perfect.

The source data for the example at hand looks like this:

Super User

Try

```Quantity Weighted Avg =
SUMX (
VALUES ( Sheet1[Name] ),
[Proportion of Total Selected Quantity] * [Avg X]
)```

Owen Auger
Blog

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 - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors