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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
scorbin-j
Helper I
Helper I

How to find the average from the sum of a measure from the last 6 months

I have two measures that sum columns from two different tables. These tables cannot be connected through a relationship, but they are connected to the same date table. 

 

SUMLaborHours = SUM(Labor[hours])
SUMDefectQty = SUM(Defects[Defect_Quantity])

 

I then find the Defects Per 1000 Labor Hours with this measure

 

DefectPerLaborHours = 
    DIVIDE(
        [SUMDefectQty],
        [SUMLaborHours],
        0
    ) * 1000

 

 

That gives these numbers for the last 6 months

8.15

2024 05
14.402024 06
7.112024 07
30.392024 08
20.262024 09
52.90

2024 10

14.082024 11

 

So I would like to find the average of those numbers. Summing them gives 147.29 which should be an average of 24.54. How can I go about getting that sum and finding that average of the last 6 months? 
Note: This is not calendar months which is why there are more than 6 months listed, but the amount of days equals 6 months. 

1 ACCEPTED SOLUTION

@scorbin-j,

 

Try adding ALLSELECTED to remove date filter context from within the visual but keeping the filter context from the filter on the graph.

 

Average Measure =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] ),
    ALLSELECTED ( 'Date'[YearMonth] )
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@scorbin-j,

 

Here's an example that assumes you've selected the six fiscal months in a slicer or filter.

 

Average Measure =
AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




So I probably should have mentioned this, but I want to show that value on a line graph showing the average as a constant line. I have filter on the graph to only go back 6 months, but that measure just seems to follow the original DefectsPerLaborHour measure instead of a constant, single value. 
Something like this: 

scorbinj_0-1731523737600.png

 

@scorbin-j,

 

Try adding ALLSELECTED to remove date filter context from within the visual but keeping the filter context from the filter on the graph.

 

Average Measure =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [DefectPerLaborHours] ),
    ALLSELECTED ( 'Date'[YearMonth] )
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That worked! Thanks for your help

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors