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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Dellis81
Continued Contributor
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.

FieldIndex.PNG

 

OneDrive link.   https://1drv.ms/u/s!AmBVCme14p7xpzem61oK7ecM29IG?e=W6ffY1

 

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
mahoneypat
Microsoft Employee
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


View solution in original post

4 REPLIES 4
Dellis81
Continued Contributor
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.

mahoneypat
Microsoft Employee
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


Dellis81
Continued Contributor
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]))

 

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
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! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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