Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JeremyCooper
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?

WeightedAvgIssue.PNG

1 ACCEPTED SOLUTION

@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...

 

Average.png

View solution in original post

5 REPLIES 5
Sean
Community Champion
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]
    )
)

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?

 

@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...

 

Average.png

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:

 

WeightedAvgIssue_SourceData.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

OwenAuger
Super User
Super User

Try

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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