Matrix with weighted Avg and Simple average within subtotals

[Dellis81]

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.

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

[Dellis81]

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 · Accepted solution]

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

[Dellis81]

@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]))

[mahoneypat]

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