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

Continued Contributor

Weighted Average

Hello - I am needing help with a weighted average calculate across multiple combination of categories.   This is a first attempt, utilizing PBI quickmeasures - unfortunately only allows one category

``````Moisture WtAvg per Field =
VAR __CATEGORY_VALUES = VALUES('Table'[Field])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE([SumMoisture] * [WetBushels])
),
SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE([WetBushels]))
)``````

I am really needing a single? measure that can handle most any combination of categories.

The three most needed are identified in the attached file - as visuals.

The blue circled should be 14.0% and Red 20.87%

Hopefully this google link with example file will work for you!

As Always - thank you!

1 ACCEPTED SOLUTION
Super User

You need to iterate at the lowest level of granularity for it to work properly at all levels.

In this case, you can iterate at the table row level to keep things simple.

``````Avg Moisture =
DIVIDE (
SUMX ( 'Table', [SumMoisture] * [WetBushels] ),
SUMX ( 'Table', [WetBushels] )
)``````

Note that this gives 14% for both numbers circled. The value 20.87% makes no sense when all of the moisture measurements are below 16%.

4 REPLIES 4
Super User

Hi,

How did you calculate the 20.87%?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Continued Contributor

Sorry, I was working off a spreadsheet, and had a transposed number.   Appreciate your time - solution has been found.

Super User

You need to iterate at the lowest level of granularity for it to work properly at all levels.

In this case, you can iterate at the table row level to keep things simple.

``````Avg Moisture =
DIVIDE (
SUMX ( 'Table', [SumMoisture] * [WetBushels] ),
SUMX ( 'Table', [WetBushels] )
)``````

Note that this gives 14% for both numbers circled. The value 20.87% makes no sense when all of the moisture measurements are below 16%.

Continued Contributor

Thank you very much.   After all my google searches, your solution is so elegant.   Apologize on the 20% number, I was working off a spreadsheet, and had a wrong number typed in.

Thank you!

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