Skip to main content
cancel
Showing results for
Search instead 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

## Matrix with weighted Avg and Simple average within subtotals

Hello!   I am working on a problem - that I thought would be straight forward.

In a farm/crop application - I am hoping to derive a average Index for fields across years and crops.  The measure CropYrRatio (blue) is my basic measure.  My first challenge, for field 12N in 2018 - there were two crops on that field, and I am wanting a weighted avg (based on acres) for the 2018 subtotal.  My next measure FieldYrRatio (red) to get to the value of: (138.8483+5.5471)/154 = .937

``````FieldYrRatio =

vAR CropYr =sumx(VALUES(Project[CropYr]),[CropYrRatio]*[Acres])

Return CropYr``````

For 2017 - with the single crop, the 2017 subtotal should be 1.076.    Then obviously for 12N (all years) average of 1.076 & .937 = 1.01

I realize this is a multi-step calculation.   My first attempt is the index by year - and then the index across all years.

Also note - for the yield calculation, average over differing crops is a meaningless number.   When I check for hasonevalue for SubheaderCrop - then calculations disappear.  For now - the wacky yield is displayed, but longer term - we need to blank out.   In the Yield measure - I do have the desired result "commented out".

Thanks for your help and assistance.  !!

1 ACCEPTED SOLUTION
Microsoft Employee

It's not entirely clear in your request, but I think this is the measure you need. You can modify as needed using the same pattern.

Average throughout the year and field of AVERAGEX(SUMMARIZE(Project, Project[CropYr], Project[Field]), [CropYrRatio]*[Acres])

Best regards

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

4 REPLIES 4
Continued Contributor

Thanks Pat for your quick response.   When I plugged your measure in - ended up with the same result as I had.   In the initial posting

To help clarify - the values circled in blue is the first calculation I am focusing on.  For Field 12N during the 2018 crop year - the field was split into two crops.   The two crops weighted average iwould be .937 or (138.8483+5.5471)/154.    I was attempting to use the "FieldYrRatio to get to this crop year weighted average.

I anticipate a 3rd calculation to get to the crop yr ratio across all years - and that value will show up in the CropYr    Thank you again.

Microsoft Employee

It's not entirely clear in your request, but I think this is the measure you need. You can modify as needed using the same pattern.

Average throughout the year and field of AVERAGEX(SUMMARIZE(Project, Project[CropYr], Project[Field]), [CropYrRatio]*[Acres])

Best regards

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Continued Contributor

@mahoneypat , thanks for your suggestion a few days ago.   After splitting this problem down into multiple measures - I was able to make work.   I then went back in and reconstruct those measures inside iterators as VAR - and ended up with this solution.
What is the difference between your suggestion of Summarize vs Values.   Appears both work the same - but are there unexpected results?   In my research - seemed both "flavors" were suggested.  Again - thank you!

``````FieldYrRatio3 =

AVERAGEX(VALUES(Project[CropYr]),
Var FieldYr = SUMX(VALUES(Center[SubHeader]),
vAR CropyRAvg =CALCULATE([Yield],allexcept(Project,Project[CropYr]),ALLEXCEPT(Center,Center[SubHeader]))
Var CropFieldYrRatio = DIVIDE([Yield],CropyRAvg,blank())
rETURN CropFieldYrRatio*[Acres])
return DIVIDE(FieldYr,[Acres]))``````

Microsoft Employee

In this type of application, SUMMARIZE is like a VALUES but for multiple columns (distinct combination of the columns).  I'm glad your measure is working, but it is using nested iterators which is a good practice to avoid.  If you run into performance issues on visual refresh with slicer changes, the measure could likely be optimized.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors