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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

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
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors
Top Kudoed Authors