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

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
Employee
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
Employee
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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.